SQL SERVER – Remove All Characters From a String Using T-SQL

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Different Methods to Extract Scale Part From Decimal Number
Next Post
Interview Question of the Week #034 – What is the Difference Between Distinct and Group By

Related Posts

No results found.

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]’, ”)

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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.

    Reply
  • Vimal Lohani
    March 8, 2016 2:12 pm

    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

    Reply

Leave a Reply

Menu