Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Stripping out all non-numeric characters from a string

DECLARE @Value NVARCHAR(200)

SET     @Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff'

WHILE @Value LIKE '%[^0-9]%'
        SET     @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')

SELECT  @Value

Print | posted on Wednesday, November 12, 2008 8:48 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Stripping out all non-numeric characters from a string


declare @string varchar(200)

set @string = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff'
select cast(cast((select substring(@string,n,1)
from numbers
where n <= len(@string)
and substring(@string,n,1) like '[^0-9]' for xml path(''))
as xml)as varchar(max))
11/12/2008 2:45 PM | Uri Dimant
Gravatar

# re: Stripping out all non-numeric characters from a string

You stripped out all numeric characters.
LIKE '[^0-9]' should be NOT LIKE '[^0-9]'

And you use
1) A tally table
2) XML

for a trivial task.
I think I will keep my original suggestion.

Thanks anyway!
11/12/2008 3:53 PM | Peso
Gravatar

# re: Stripping out all non-numeric characters from a string

Oh! I now realized you may get the digits in wrong order depending on index for tally...
11/12/2008 4:03 PM | Peso
Gravatar

# re: Stripping out all non-numeric characters from a string

How about something a bit more generic?

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/pattern-based-replacement-udf.aspx

This will strip all non-numeric characters, or all numeric characters, or all punctuation marks, or whatever else you want...
11/12/2008 8:34 PM | Adam Machanic
Gravatar

# re: Stripping out all non-numeric characters from a string

Yes, we had an exercise like that here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083
11/12/2008 10:13 PM | Peso
Gravatar

# re: Stripping out all non-numeric characters from a string

ALTER function [dbo].[fn_ReplaceNotAlfaChar] (@STR varchar(max))
returns varchar(max)
--IQSYSTEMS LTD
as
begin
if patindex('%[^a-z ]%',@STR)=0 return @STR
while patindex('%[^a-z ]%',@STR)>0 select @STR=stuff(@STR,patindex('%[^a-z ]%',@STR),1,' ')
return @STR
end
11/14/2008 11:21 AM | Vitaly Zabavin
Gravatar

# re: Stripping out all non-numeric characters from a string

SET @Value = '²³¹¼½¾'
11/15/2008 3:21 PM | Arnold "Collation" Fribble
Gravatar

# re: Stripping out all non-numeric characters from a string

Ok, I will figure out fractions too... ;-)
11/15/2008 5:56 PM | Peso
Gravatar

# re: Stripping out all non-numeric characters from a string

Nice Post
8/10/2010 9:34 AM | dineshrajan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET