How can i get the numeric values in string?

Question

0.00/5 (No votes)

See more:

Hi all experts,

I have one string with format like this:yyyyyyxx
yyyyy: is the numeric
xx: can be cm,m,mm,dm,.....
EX:1234m,12342cm,123456mm
My question is how i can get the numeric(1234 or 12342,....) from that string?

Thanks

TONY

Posted 3-Aug-12 15:50pm

soeun tony

Add a Solution

Comments

[no name] 3-Aug-12 21:57pm

SubString it

Kenneth Haugland 3-Aug-12 22:24pm

Cant you do basic aritmatic and replace in SQL?

[no name] 3-Aug-12 22:37pm

He's not asking to do arithmetic. He is just asking to extract a string from another and maybe convert.

Kenneth Haugland 3-Aug-12 22:43pm

Then SubString is definatly the way to go. I dont understand how you could use that if they all warent the same, but thats another issue.

[no name] 3-Aug-12 22:44pm

All he has to do is string length - 2 if the last 2 is the measurement type. But that is his problem. Don't you ever sleep?

Kenneth Haugland 3-Aug-12 22:46pm

No, cant you tell by my answer? :)

[no name] 3-Aug-12 22:48pm

Thought it was the wrong time of year for the midnight sun over there

Kenneth Haugland 3-Aug-12 22:50pm

Its the right time of year, but not were I live. Besides I was a sleep, it very early morning here now....

[no name] 3-Aug-12 22:53pm

Of course now that I think about it... Well you might not sleep but I do :-) Have a good one.

Kenneth Haugland 3-Aug-12 22:47pm

Could be only m also....So he is goint to have to check that as well-

2 solutions

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)

Santhosh Kumar Jayaraman · Answer 1 · 2012-08-03T20:30:00

Use this function

SQL

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS int
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN Cast(@IntNumbers as int)
END
GO

Then call that in select query.

SQL

Select col1,dbo.ExtractInteger(col1) from tbl_temp

Kenneth Haugland · Answer 2 · 2012-08-03T15:57:00

Solution 1

Looks like the simple answer is do it yourself:
http://msdn.microsoft.com/en-us/library/cc295312%28v=expression.40%29.aspx[^]

Posted 3-Aug-12 15:57pm

Kenneth Haugland

Comments

soeun tony 3-Aug-12 22:13pm

Hi Kenneth,
It is an example that i made myself and the real information i got also like this.
Based on your link, it is in Visual Studio....but i want to do it in SQL Server.

Thanks

TONY

Kenneth Haugland 3-Aug-12 22:19pm

If its above m then multyply, if its belov devide:
http://en.wikipedia.org/wiki/Metric_prefix

or you could do it the way Wes suggested.