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 |
|
Claire_Toronto
Starting Member
11 Posts |
 Posted - 2011-08-08 : 20:57:30
|
| Hi,I have a string and would like to extract from the right side the numeric portion...Example:E4_R5tg_20100408 (here I need 20100408)Z2_Swdcx_20100408winds (here I need 20100408)so in the first example I only need 8 digits from the right and in the 2nd example, I need to ignore winds and then extract 8 digits...theres 50K rows but winds is the same text in all the cells and the others are simply digits...thanksClaire |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-08 : 23:04:55
|
| Claire, this can be done through a variety of ways. The success of the outcome depends largely on the consistency of the data. So is it always going to be "winds" in the example you gave? There are going to be more elegant solutions that check all the possible problems, but this will get you what you need from the info you gave:SELECT CAST(CASE WHEN RIGHT(STRING,5)='WINDS' THEN right(REPLACE(STRING,'winds',''),8) ELSE RIGHT(STRING,8) END AS INT)FROM YourStringTable |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2011-08-09 : 02:41:36
|
| [code]try like thisDECLARE @string VARCHAR(64)SELECT @string = 'Z2_Swdcx_20100408winds'SELECT REPLACE(PARSENAME(REPLACE(@string,'_','.'),1),'winds','') -- it will work upto 4 values....SELECT REPLACE(SUBSTRING(@string,LEN(@string)-CHARINDEX('_',REVERSE(@string))+2,LEN(@string)),'winds','')[/code] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Claire_Toronto
Starting Member
11 Posts |
Posted - 2011-08-10 : 16:35:57
|
| Thanks everyone! |
|
|
|
|
|
|
|
|
Extract numeric part of string from right