Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a string field in a table that conatains values like 100S,200Mg,5Yrs. How can I take only the number part from this field by using a select statement.
OriginalGriff 28-Apr-11 6:22am    
Which number?
Your example contains three numbers: 100, 200, and 5
I would assume this is a description of a capsule of similar: 100 per bottle, 200Mg active ingredient, 5Years and older only. But how is a computer supposed to know that?

try this

DECLARE @NumStr varchar(1000)
SET @NumStr = '100S';
WHILE PATINDEX('%[^0-9]%',@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')
Share this answer
Ankur\m/ 28-Apr-11 6:30am    
That should help. I have never used Regular expression in SQL. Learned something new. 5!
RaviRanjanKr 28-Apr-11 7:01am    
Nice Answer! My 5 :)
Mahendra.p25 28-Apr-11 7:48am    
Declare @S As VarChar(15)
Set @S = '   5000.00/- RS'
Select Right(@S, 2) As Rupees, Left(@S, 10) As Amount
Select Substring(@S, 14, 2) As Rupees, Substring(@S, 1, 13) As Amount
Share this answer
RDBurmon 28-Apr-11 7:25am    
Sorry , But not a right way of doing . You can not bind your solution to work on specifice type of input. See the proper way read Mahen Reply.
You can try like that
using System.Text.RegularExpressions;

string s = "100S,200Mg,5Yrs";
Regex r = new Regex("[a-zA-Z]");
s = r.Replace(s, "");

output would be

I am just replacing all the charcters with space, if you have some other char then create your own RegEx.
Share this answer
Ankur\m/ 28-Apr-11 6:27am    
OP has asked for a solution in SQL and not C#.
nit_singh 28-Apr-11 6:47am    
Yes right Ankur...but RegEx is faster than the while loop so he can pass the data to the frontend and aply this logic.
Ankur\m/ 28-Apr-11 7:01am    
Fine, but you never know about someone's requirement. You also haven't mentioned in the answer that this approach is better and why.
Ankur\m/ 28-Apr-11 7:01am    
BTW see Mahen's answer. It also uses RegEx.
RDBurmon 28-Apr-11 7:26am    
totally agreed with Ankur. Thanks to Mahen for good post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900