| Author |
 Topic  |
|
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 12/13/2012 : 10:53:20
|
Hi guys,
I need to make some string to numeric. See following:
Cast(Right(CodeNum, 1) as Int)
But the dilemma is CodeNum has values as ABC3, BBC14. I know I can make the CodeNum as ABC03 and change the Right() function to Right(CodeNum, 2) but for some reason, it will take a lot of work to do that. So what is the easier way to solve this?
Appreciate your time! |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3385 Posts |
Posted - 12/13/2012 : 11:04:25
|
patindex('%[^0-9]%',CodeNum) will give the position of the first non numeric character
to start from the end patindex('%[^0-9]%',reverse(CodeNum))
So the number of numeric characters is patindex('%[^0-9]%',reverse(CodeNum)) - 1
therefore
convert(int,right(CodeNum,patindex('%[^0-9]%',reverse(CodeNum))-1))
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 12/13/2012 : 11:19:06
|
wow, this is something looks like c#, thank you so much.
This is great, thanks!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 12/14/2012 : 01:39:05
|
STUFF(field,1,PATINDEX('%[0-9]%',field)-1,'')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Is this possible?