How do i change a string key to an int - normal methods not working!

Question

How do i change a string key to an int - normal methods not working!

isuckatsql

SSChampion

Points: 10381
More actions
April 13, 2014 at 2:14 pm

#306695

did VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
Is that the only solution ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

isuckatsql (4/13/2014)
did VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
Is that the only solution ?
Thanks

Quick question, what is the integer representation for? Unless this is a number of base 35 or 36, I cannot see any obvious and meaningfull conversion.:cool:

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Riddle me this: what integer do you expect from this value: 'cht6p575l7ry61k1npt'??

any non-integer values need to be discarded. Unfortunately that is really not that easy BEFORE SQL 2012. But in the version you are posting against you have some new TSQL tools, namely TRY_CONVERT(). I leave it to you to hit Books Online or your resource of choice and work out the solution for yourself.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

isuckatsql SSChampion Points: 10381 More actions · Answer 3

Based on research and the lack of response, i guess it can't be done in SQL.

Let's try C#.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

isuckatsql (4/14/2014)
Based on research and the lack of response, i guess it can't be done in SQL.
Let's try C#.

Again, answer the question asked below, what value do you expect this ( 'cht6p575l7ry61k1npt') to be converted to as an integer?

TheSQLGuru (4/13/2014)
Riddle me this: what integer do you expect from this value: 'cht6p575l7ry61k1npt'??
any non-integer values need to be discarded. Unfortunately that is really not that easy BEFORE SQL 2012. But in the version you are posting against you have some new TSQL tools, namely TRY_CONVERT(). I leave it to you to hit Books Online or your resource of choice and work out the solution for yourself.