Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-12-13 : 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-13 : 11:04:25
|
| patindex('%[^0-9]%',CodeNum) will give the position of the first non numeric characterto start from the end patindex('%[^0-9]%',reverse(CodeNum))So the number of numeric characters is patindex('%[^0-9]%',reverse(CodeNum)) - 1thereforeconvert(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 - 2012-12-13 : 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
52326 Posts |
Posted - 2012-12-14 : 01:39:05
|
| STUFF(field,1,PATINDEX('%[0-9]%',field)-1,'')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Is this possible?