CODEWITHSUNDEEP
sqlsql-server
Andrew Martin
Andrew Martin

Reputation: 151

SQL Server Extract INTs from VARCHAR

I know this looks like a duplicate, but I don't see the answer I'm looking for yet.

I have an ID column that is VARCHAR(36) for ID tokens. In a legacy system, these tokens are only INT. Now they are all mixed up, and I need to be able to extract only the integer tokens for certain operations, and they have to be converted to INTs.

I thought I could do that by this:

select top 100000 convert(int,CustomerToken) as CustomerToken
from Customer
WHERE (CustomerToken NOT LIKE '%[^-+ 0-9]%' AND CustomerToken < '214783647')

But there are some numeric values that I don't want. But some numeric values give problems. I get the error varchar value '11122241333' overflowed an int column.

This seems really odd to me. This seems even weirder:

SELECT 1 WHERE '11122241333' < '2147483647'

1

????

Has anyone seen this before? Anyone know what I can do about it?

Upvotes: 0

Views: 64

Answers (2)

Greenstone Walker
Greenstone Walker

Reputation: 1150

If you have SQL Server 2012 or later then you can use try_parse().

SELECT TOP 100000 try_parse( CustomerToken as int ) AS CustomerToken

If the value cannot be cast then the function will return a null marker, otherwise it will return an int.

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

You may try to "LPAD" your strings (I choose 20 as max length, which is of course arbitrary), if you want a "pseudo-number" comparison.

select top 10000 convert(int, CustomerToken) as CustomerToken
from Customer
WHERE (CustomerToken NOT LIKE '%[^-+ 0-9]%' 
AND replicate('0', 20- len(CustomerToken)) + CustomerToken < '0000000000214783647')

Upvotes: 2

Related Questions