Converting String to Int | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Converting String to Int

I am in the process of migrating old address data from one table into another table. The old table consists of all colunms being varchar fields. One of the mappings is to convert a varchar field to numeric, the issue is that about 15% of them contain not numeric characters and are failing when attempting to convert to a numeric. declare import_cursor Cursor for
select metroid, street, apt, zip, lastname, house, cr
from tblmetro open import_cursor Fetch next from import_cursor into @id, @str, @apt, @zip, @name, @house, @Cr2 While @@fetch_status=0
begin
if (@Cr2 is null or @Cr2 =”)
begin
select @street2=CSBPSstreet from tblstreetlookup where [email protected] –perform streetlookup for all csbps modified streets
if (@street2 is not null and len(@street2) >0)
begin
select @[email protected]
update tblmetro set [email protected] where [email protected] –Update the CR based on zip,house range, street
exec spCRLookup @house, @str, @zip, @outputCR output
update tblmetro set [email protected] where [email protected]
end
end Fetch next from import_cursor into @id,@str, @apt, @zip, @name,@house,@Cr2
end
close import_cursor
deallocate import_cursor
The process never completes, once I recieve the following error: Syntax error converting the varchar value ‘222613-1’ to a column of data type int. my cursor stops.
Is there a way to check/pattern match to see if the value in the house column is a valid numeric? If it is continue with the process or skip the record and continue on.
Thanks in advance for your time.
lookup ISNUMERIC in BOL
If the varchar value has only numerals it will work. Otherwise
You need to check for the condition whether it is numeric. If it is numeric again you need to check whether is has decimal point. If it has, you need to omit it and take integer part only if Isnumeric(VarcharField)=1
–Valid.
intcol=Convert(int,Convert(float,VarcharField)) Madhivanan Failing to plan is Planning to fail
Hi,
I had a similar task…i ended up using something like IF ColName NOT LIKE REPLICATE(‘[0-9]’, len(ColName))
BEGIN…. although ISNUMERIC is probably a much better way to do it…
quote:Originally posted by Madhivanan If the varchar value has only numerals it will work. Otherwise
You need to check for the condition whether it is numeric. If it is numeric again you need to check whether is has decimal point. If it has, you need to omit it and take integer part only if Isnumeric(VarcharField)=1
–Valid.
intcol=Convert(int,Convert(float,VarcharField)) Madhivanan Failing to plan is Planning to fail
Best way will be : First find out whether the field value is numeric or not ELSE replicate the non-numeric values and store.
Surendra Kalekar
Thanks for the all the replys I’ll look into the isnumeric function.
Just have a look at this:http://www.sql-server-performance.com/q&a127.asp
ISNUMERIC can be tricky at times. However with the convertion to INT only, it should be fairly safe.

Frank Kalis
SQL Server MVP
http://www.insidesql.de

]]>