In this post, How to Remove All Characters From a String Using T-SQL? we have seen How to Remove All Characters From a String Using T-SQL. But it only removes characters from the string. What if the string has some other special characters? Here is the method that handles this
SET NOCOUNT ON DECLARE @loop INT DECLARE @str VARCHAR(8000), @output VARCHAR(8000) SELECT @str = 'ab123ce23,4f$e', @output='' SET @loop = 1 WHILE @loop < LEN(@str) BEGIN SET @output=@output+CASE WHEN ASCII(SUBSTRING(@str,@loop,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@str,@loop,1) ELSE '' END SET @loop = @loop + 1 END SELECT @output
Now here is the result:
123234
The idea here is instead of finding and removing non numerics, find only numbers and concatenate them. You can use if the ASCII value of a string is between 48 and 57 (which is a digit from 0 to 9). If it is in the range, it is a digit then concatenate it. This way we can remove anything which is not a number.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
Also, you can implement regular expression support in SQL Server as it explained here
And execute this query:
select dbo.RegExReplace(‘ab123ce23,4f$e’,'[^0-9]’, ”)
Thanks for the tip. I may be asking a silly question, but what is the advantage of using the ASCII value between 48 and 57 instead of between ‘0’ and ‘9’?
Like so…
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR(8000), @output VARCHAR(8000)
SELECT @str = ‘ab123ce23,4f$e’, @output=”
SET @loop = 1
WHILE @loop < LEN(@str)
BEGIN
SET @output=@output+CASE WHEN SUBSTRING(@str,@loop,1) BETWEEN '0' AND '9' THEN SUBSTRING(@str,@loop,1) ELSE '' END
SET @loop = @loop + 1
END
SELECT @output
Why use a loop when you can write a set-based version that performs about five times faster?
SET NOCOUNT ON;
DECLARE @str VARCHAR(8000), @output VARCHAR(8000);
SELECT @str = ‘2&>&F&cply?R9XZ.V]!*ZM?w~%avDhv&;`89@wR@FYrYW>GT”/ST)5t#~w=fNOF\q/Vly.;bfvzIA(8W8uFRMR”r16Egq{8ps>”aEIOIcV_?’, @output=”;
WITH N1 AS (SELECT n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0))v(n))
, N100 AS (SELECT m1.n FROM N1 m1 CROSS JOIN N1 m2)
, Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY n) n FROM N100)
SELECT @output = @output + CASE WHEN ASCII(SUBSTRING(@str,n,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@str,n,1) ELSE ” END
FROM Numbers
WHERE n <= LEN(@str);
SELECT @output
The above is for illustrative purposes only – I suspect that if you use a FOR XML clause, you could get it running faster still. I didn't do that because XML mangles characters such as "” and “&” and you need to handle that explicitly.
John
Great. Thanks for sharing! Still a lot to learn…
This does not require any loop code in the sql:
declare @string varchar(8000) = ‘ab123ce23,4f$e’,
@charsToKeep varchar(50) = ‘[0-9]’;
with
oneSet1 (i) as (select i from (values (1),(1),(1),(1),(1),(1),(1),(1)) a (i)), — 8
oneSet2 (i) as (select 1 from oneSet1 a cross join oneSet1 b), — 8^2 (64)
nbrList (i) as (select row_number() over (order by i) – 1 as i from oneSet2)
select stuff((
select substring(@string, nl.i, 1)
from nbrList nl
where substring(@string, nl.i, 1) like @charsToKeep
for xml path(”)), 1, 0, ”) as newText;
The inner select turns the string into a table of char(1) and selects only those character we wish to keep. The outer select uses “STUFF()” to convert the table back into a string. Very fast even with large strings. It can be turn into a ILTF that can you can croos apply to a column in a table.
That’s really good one!
Find only characters from string :
DECLARE @Temp VARCHAR(100)=’548STA123Ldfgh45df45df5446fg54645dfg546′;
Declare @NumRange AS varchar(50) = ‘%[0-9]%’;
While PatIndex(@NumRange, @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,”)
SELECT @Temp
====================================
Find only integers from string :
DECLARE @var VARCHAR(100)=’5STA123Ldfgh45df45df5446fg54645dfg546′;
DECLARE @intdata INT
SET @intdata = PATINDEX(‘%[^0-9]%’, @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, ” )
SET @intdata = PATINDEX(‘%[^0-9]%’, @var )
END
END
Select ISNULL(@var,0)
Find a float value from string :
DECLARE @var VARCHAR(100)=’STA123.26dfg’;
DECLARE @intdata INT
SET @intdata = PATINDEX(‘%[^0-9]%’, @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, ” )
SET @intdata = PATINDEX(‘%[^0-9.]%’, @var )
END
END
Select ISNULL(@var,0)
=====================================
Reference : http:// dbavimal.blogspot.in/2014/09/find-only-characters-or-only-integer. html
I have not tested it but looks clean.