extract numeric part from string ..lenght f string is varrying ..

Question

extract numeric part from string ..lenght f string is varrying ..

SHR

SSC Veteran

Points: 232
More actions
March 23, 2010 at 3:40 am

#216905

Hello Guys,
i need to extract numeric part of string and based on extracted numeric part i have to group the retrieved data.
String can be like this "mdng123kjab"
my requirement is 123 to be extracted from the string. lenghth of string is always varrying so left,right functions cant be used in this case.
I will appreciate ur timely response.
Regards,
Shekhar

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ferry.jansen SSC Enthusiast Points: 196 More actions · Answer 1

raut.sh (3/23/2010)
Hello Guys,
i need to extract numeric part of string and based on extracted numeric part i have to group the retrieved data.
String can be like this "mdng123kjab"
my requirement is 123 to be extracted from the string. lenghth of string is always varrying so left,right functions cant be used in this case.
I will appreciate ur timely response.
Regards,
Shekhar

You will want to use the RegEx function(Regular Expression). This function will let you change the format of strings.

I'm not very familliar with this function so I can't provide much assistance. But it is possible.

http://msdn.microsoft.com/en-us/library/ms157328.aspx for more information(search for RegEx)

Lowell SSC Guru Points: 323518 More actions · Answer 2

you can do it with a tally table as well;

for example the function I'm pasting below will strip out everything except chars in my specific range of ascii characters:

--ascii numbers are 48(for '0') thru 57 (for '9')

select dbo.StripNonNumeric('34mm56oo')--3456

CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;WITH tally (N) as

(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END

FROM tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SHR SSC Veteran Points: 232 More actions · Answer 3

I used follwing fuction, m now able to extract numbers from string

--

Shared Function ExtractNumbers( ByVal expr As String ) As String

Return String.Join( Nothing, System.Text.RegularExpressions.Regex.Split( expr, "[^\d]" ) )

End Function

--

now i need to drill down again, i need to retrive numbers which are after first space and before second space in a given string.

exp. given string: ABC n890n n770

i want to retrieve only 890 which after first space and before second space in the above string

Plz help me getting it done...

Regards,

Shekhar