Convert Field From VarChar To Int With Speical Characters In Field

Hello,

I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.

How can I remove all speical characters and then convert that field to an int?

Also I tried the following query which did not work as well, same error about conversion.

UPDATE myTable SET field = CAST(field AS int)

If you put some code logic like what I have below into a user-defined function, it should remove anything at the end of your number value that you want to eliminate. Then you can use an update statement and use the UDF for the new value. This will only work if the extraneous text is at the end of the values though.

declare @.a nvarchar(100)

set @.a = '15483ktr'

declare @.numpart nchar(1)

declare @.x int

set @.x = 1

set @.numpart = SUBSTRING(@.a,@.x,1)

While IsNumeric(@.numpart) = 1

BEGIN

set @.x = @.x + 1

set @.numpart = SUBSTRING(@.a,@.x,1)

END

print SUBSTRING(@.a, 1, @.x-1)

|||

This trims all characters LEFT of the first Numeral, and all characters RIGHT of the first NON-numeral:

ABC123 becomes 123

123ABC becomes 123

ABC1234DEF becomes 1234

ABC12.34 becomes 12

1234ABC456 becomes 1234

ABC1234DEF5678 becomes 1234

For this reason, I've remarked the Update statment, so you can run this on your table and see what turns into what

Code Snippet

--Update MyTable set <MyField> =

select <MyField>,

left(substring(<MyField>, patindex('%[0-9]%',<MyField>),8000) + 'A',

patindex('%[^0-9]%',substring(<MyField>, patindex('%[0-9]%',<MyField>),8000) + 'A')-1)

from <MyTable>

where <MyField> like '%[0-9]%'