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 |
|
Maithil
Starting Member
29 Posts |
 Posted - 2015-04-01 : 01:40:36
|
| Hi All,I want the Numeric Value from Combination of alphabets(a-z,A-Z) , Special Characters and Numeric Value.example :: I have '13$23%as25_*'and query should return --> 132325 as result.Thanks in Advance. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-04-01 : 02:38:09
|
| declare @string varchar(100), @number_only varchar(100)select @string='13$23%as25_*',@number_only =''select @number_only=@number_only +string from(select substring(@string,number+1,1) as string from master..spt_values where type='p' and number<len(@string)and substring(@string,number+1,1) like '%[0-9]%') as tselect @string as string,@number_only as number_onlyMadhivananFailing to plan is Planning to fail |
 |
|
|
Maithil
Starting Member
29 Posts |
Posted - 2015-04-01 : 03:01:36
|
| Hi madhivanan,Thanks for your reply. It works Perfectly.But In My Product, I have one table Of Currency_symbol with data of Well-known Currency like Dollar($), etc...So If any of symbol of currency_table is there in the string... so that symbol should be skipped. e.g.If in My String I have 'as@1245$%' then the result should be 1245$.I appreciate your effort.And this Logic will apply many times.Thanks in Advance. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-04-01 : 06:07:23
|
| Instead of '%[0-9]%' use '%[0-9$]%'. If you want to apply it for a column value, create this as a function and call it in the SELECT statmentMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 06:37:09
|
quote:
Originally posted by Maithil
If in My String I have 'as@1245$%' then the result should be 1245$.
What would the correct answer be for '13$23%as25_*'Is 13$2325 correct? (Seems unlikely, thats all!  ) |
|
|
|
Maithil
Starting Member
29 Posts |
Posted - 2015-04-01 : 06:48:44
|
| @madhivanan Thanks a lot @Kristen : It is based on Requirement... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-04-01 : 08:31:31
|
quote:
Originally posted by Kristen
quote:
Originally posted by Maithil
If in My String I have 'as@1245$%' then the result should be 1245$.
What would the correct answer be for '13$23%as25_*'Is 13$2325 correct? (Seems unlikely, thats all! )
Good catch. In that case extract numbers and append $ at the end. But I would prefer doing this in a front end application if it is involved in this process MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 13:00:58
|
quote:
Originally posted by madhivanan
In that case extract numbers and append $ at the end
I did wonder if, in that case, different rules might apply - such as "All numbers before the '$' only"The Requirements could be anything really ...... perhaps better to clean the data (or parse it into component parts) and store it in the database in that form - much easier to then use it safely thereafter, and if the parsing / cleaning requirements change they can be adjusted at the point where the data is imported / acquired, and then everything "downstream" is using using the, clean, data |
|
|
|
|
|
|
|
|
Fetch Numeric Value from string