I want to get only numeric values from description

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I want to get only numeric values from description

Author

Topic

Thiyagu_04
Starting Member

37 Posts

Posted - 2011-10-04 : 02:52:38

Hi,

I need to get list of only numeric values in the description column.

For eg In a table contain description column

Description column

'the details of 3909 values 4984988'
'skdjkjsdfjkl 903890 sjdfjk 230'

Required Output
3909,4984988
903890,230

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 02:59:29

[code]SELECT t.Description,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.Description,' ')f
WHERE f.Val NOT LIKE '%[^0-9]%'
[/code]

ParseValues can be found in below link
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thiyagu_04
Starting Member

37 Posts

Posted - 2011-10-04 : 03:24:05

I tried with bellow query it goes infinite loop

select * from dbo.ParseValues('the lasdjkf 3909 jlksjdf 4984988',' ')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-04 : 03:52:43

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

KH
[spoiler]Time is always against us[/spoiler]

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-04 : 07:14:53

try the followin function :

create FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
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
GO
----test using
SELECT dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 07:30:27

quote:
Originally posted by Thiyagu_04

I tried with bellow query it goes infinite loop

select * from dbo.ParseValues('the lasdjkf 3909 jlksjdf 4984988',' ')

i think you're not using it as suggested. its working as expected for me.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-10-04 : 07:53:25

Check out this function - http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html

SELECT * FROM dbo.[SplitUsingXML]('the details of 3909 values 4984988', ' ')
WHERE parsedValue NOT LIKE '%[^0-9]%'

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources