Select number part from a value

Question

0.00/5 (No votes)

See more:

SQL-server-2000

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 28-Apr-11 0:11am

Sanyon_d

Add a Solution

Comments

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?

3 solutions

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

Posted 28-Apr-11 0:29am

anvas kuttan

Comments

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.

Posted 28-Apr-11 0:21am

nit_singh

Updated 28-Apr-11 0:23am

v2

Comments

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mahendra.p25 · Accepted Answer · 2011-04-28T00:20:00

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

Posted 28-Apr-11 0:20am

Mahendra.p25

Comments

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

Thanks