SQL - Convert a 9 byte Character column to an Integer 2

[DWSRAMBO]

I am trying to convert a 9 digit character column to an integer column.

I'm not sure whether the field has spaces or non-numeric characters. I can Ltrim/Rtrim the spaces off either end before converting but if there are spaces or non-numeric characters within I need to make the resulting field NULL.

I would appreciate any Ideas.

Thanks
Rick

 

[btgroup]

You can use isnumeric to figure out if the value can be converted.

SELECT convert(numeric(9,0), fieldname) FROM table1
WHERE isnumeric(fieldname) = 1

Isnumeric will return 1 if the value can be converted to an integer and 0 if it cannot.

 

[nikhilparchure]

The above was a pretty decent soln.
Added to my knowledge base
Thanks

Parchure Nikhil

 

[nigelrivett]

>> You can use isnumeric to figure out if the value can be converted.

Unfortunately you can't if you want to convert to numeric(9,0) only.
Try
create table #a(s varchar(9))
insert #a select '12e23'
insert #a select ' +1223'
SELECT convert(numeric(9,0), s) FROM #a
WHERE isnumeric(s) = 1

you need to also check for non digits

SELECT convert(numeric(9,0), s) FROM #a
WHERE isnumeric(s) = 1
and patindex('%[^0-9 -+]%',s) = 0




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.