Riddle me this: - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Riddle me this:

Author

Topic

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-17 : 16:44:14

[code]UPDATE t
SET t.id = CASE
WHEN ISNUMERIC(t.name) = 1 THEN CAST(t.name AS BIGINT)
ELSE NULL END
FROM
Table t
WHERE
t.id IS NULL;[/code]
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

Now the "name" column is varchar(10) and the "id" column is bigint, but for the life of me I cannot figure out why this doesn't work.

Ideas?

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 16:48:41

[code]UPDATE Table1
SET id = name
WHERE id IS NULL
AND name NOT LIKE '%[^0-9]%'[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 16:49:59

Yes. If the name is for example "1e5000" the SQL engine tries to interpret that as 10^5000, which is a number far greater than can be stored as a BIGINT.

E 12°55'05.63"
N 56°04'39.26"

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-17 : 16:55:50

Well that sucks. The error message is rather misleading.

OK thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 16:57:55

The suggestion posted 06/17/2009 : 16:48:41 works for you?

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-17 : 17:02:47

As Peso pointed out it could be a size issue. What is implied but not stated is that using the ISNUMERIC function is not reliable, thus Peso showed the "NOT LIKE '%[^0-9]%'" integer filter. Additionally, SQL doesn't handle certain conversion well/properly. Here is a quick sample:[CODE]DECLARE @Table TABLE (Val VARCHAR(10))

INSERT @Table
SELECT '6.6'
UNION ALL SELECT '6'

-- Works (but with rounding error)
SELECT CAST(CAST(Val AS DECIMAL(18,0)) AS BIGINT)
FROM @Table

-- Fails without WHERE clause.
SELECT CAST(Val AS BIGINT)
FROM @Table
-- WHERE Val NOT LIKE '%[^0-9]%'[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources