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
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
Legacy Comments
|
Uri Dimant
2008-11-12 |
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)) |
|
Peso
2008-11-12 |
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! |
|
Peso
2008-11-12 |
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... |
|
Adam Machanic
2008-11-12 |
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... |
|
Peso
2008-11-12 |
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 |
|
Vitaly Zabavin
2008-11-14 |
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 |
|
Arnold "Collation" Fribble
2008-11-15 |
re: Stripping out all non-numeric characters from a string SET @Value = '²³¹¼½¾' |
|
Peso
2008-11-15 |
re: Stripping out all non-numeric characters from a string Ok, I will figure out fractions too... ;-) |
|
dineshrajan
2010-08-10 |
re: Stripping out all non-numeric characters from a string Nice Post |