15,849,829 members
See more:
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.
Posted
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?

## Solution 1

try this

SQL
```DECLARE @NumStr varchar(1000)
SET @NumStr = '100S';
BEGIN
WHILE PATINDEX('%[^0-9]%',@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')
END
PRINT @NumStr```

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
Mahendra.p25 28-Apr-11 7:48am
Thanks

## Solution 3

SQL
```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```

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.

## Solution 2

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
`100,200,5`

I am just replacing all the charcters with space, if you have some other char then create your own RegEx.

v2
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.