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 |
|
rev1976
Starting Member
2 Posts |
 Posted - 2010-08-27 : 09:35:37
|
| Hi all,I'm new to SQL so please bare with me. Here's my question. I need to do the following 1) Retrieve the numbers only but here is the type of data in the table:ABC-1234-DEFGHI-5678HJK-7890YOU-345QWE-91I need to filter out the letters and '-' so the result is only the numbers. Here's my code so far (not sure if it's correct or if there's a easier way to right it)Case when CharIndex('-', (Substr(WORD,5,LEN(WORD))) > 0 THEN Substring(Substring(WORD,5,LEN(WORD)),1,(CharIndex('-', WORD)-1)) ELSE Substring(WORD,5,LEN(WORD)) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-27 : 10:21:38
|
| http://beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspxMadhivananFailing to plan is Planning to fail |
 |
|
|
rev1976
Starting Member
2 Posts |
Posted - 2010-08-27 : 13:08:00
|
| Thanks madhivanan. Can you please explain what each step is doing? Thank you again. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-31 : 05:45:07
|
quote:
Originally posted by rev1976
Thanks madhivanan. Can you please explain what each step is doing? Thank you again.
It sperates each character and see if it is a digit. If it is a digit it will be concatenated to a variable otherwise it will be omittedMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Trimming the text