Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do i change a string key to an int - normal methods not working! Expand / Collapse
Author
Message
Posted Sunday, April 13, 2014 2:14 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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
Post #1561298
Posted Sunday, April 13, 2014 3:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 1,543, Visits: 4,272
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.
Post #1561302
Posted Sunday, April 13, 2014 7:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 4,343, Visits: 6,149
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 at GMail
Post #1561306
Posted Monday, April 14, 2014 8:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Based on research and the lack of response, i guess it can't be done in SQL.

Let's try C#.
Post #1561509
Posted Monday, April 14, 2014 9:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 23,218, Visits: 31,905
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1561531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse