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

In the recent past I have seen the best interaction and learning happens when we all get into a puzzle and challenge mode. And that just gets the creative juice in our minds and some really innovative solutions are given. So this blog is written like a nice trivia solution and I would love to hear from you on potential alternate solutions to this as you read the simplified solution.

There are many situations where a developer needs to do a lot of string manipulation using T-SQL. There are many things which ideally should be done via SQLCLR.

SQL SERVER - How to Remove All Characters From a String Using T-SQL? chars

One of my blog reader posted a comment recently

I want to get only integer part in string. for example, string contain ab123ce234fe means i want only integer part like 123234. how can i get?

I gave him below suggestion.

SET NOCOUNT ON
DECLARE
@loop INT
DECLARE
@str VARCHAR(8000)
SELECT @str = 'ab123ce234fe'
SET @loop = 0
WHILE @loop < 26
BEGIN
SET
@str = REPLACE(@str, CHAR(65 + @loop), '')
SET @loop = @loop + 1
END
SELECT
@str

Do you think there is a better way to achieve that? Please write it in comments section. It is going to be a great learning opportunity for all.

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

Previous Post
SQL SERVER – Puzzle – Inside Working of Datatype smalldatetime
Next Post
Interview Question of the Week #030 – Retrieve Last Inserted Identity of Record

Related Posts

15 Comments. Leave new

  • If you expect few instances of characters it may save time to test for characters before going through all 26 letters:

    SET NOCOUNT ON
    DECLARE @loop INT
    DECLARE @str VARCHAR(8000)
    SELECT @str = ‘ab123ce234fe’
    SET @loop = 0
    WHILE @loop < 26 AND PATINDEX('%[a-z]%',@str) 0
    BEGIN
    SET @str = REPLACE(@str, CHAR(65 + @loop), ”)
    SET @loop = @loop + 1
    END
    SELECT @str

    Or even only looping through as many times as non-integer characters you have:

    SET NOCOUNT ON
    DECLARE @str VARCHAR(8000) = ‘ab123ce234fe’
    DECLARE @pat INT = PATINDEX(‘%[a-z]%’,@str)
    WHILE @pat 0
    BEGIN
    SET @str = LEFT(@str,@pat-1) + RIGHT(@str,LEN(@str)-@pat)

    SET @pat = PATINDEX(‘%[a-z]%’,@str)
    END
    SELECT @str

    Reply
  • This can also be done by using below query

    CREATE FUNCTION udf_extractInteger(@string VARCHAR(2000))
    RETURNS VARCHAR(2000)
    AS

    BEGIN
    DECLARE @count int
    DECLARE @intNumbers VARCHAR(1000)
    SET @count = 0
    SET @intNumbers = ”

    WHILE @count =’0′ and SUBSTRING (@string, @count, 1) <='9'
    BEGIN
    SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
    END
    SET @count = @count + 1
    END
    RETURN @intNumbers
    END
    GO

    select dbo.udf_extractInteger('ab123ce234fe')

    Reply
  • DECLARE @KeepValues AS VARCHAR(50),
    @Str AS VARCHAR(500)
    SET @KeepValues = ‘%[^a-z]%’
    WHILE PATINDEX(@KeepValues, @Str) > 0
    SET @Str = STUFF(@Str, PATINDEX(@KeepValues, @Str), 1, ”)

    Reply
    • short and simple solution !! Thanks for sharing :)

      Reply
      • For this case, its going to be as following:
        DECLARE @KeepValues VARCHAR(50),@Str VARCHAR(500) = ‘ab123ce234fe’
        SET @KeepValues = ‘%[^a-z]%’ — if you want only characters in the output
        SET @KeepValues = ‘%[^0-9]%’ — If you want only integers in the output
        WHILE PATINDEX(@KeepValues, @Str) > 0
        SET @Str = STUFF(@Str, PATINDEX(@KeepValues, @Str), 1, ”)
        select @Str

      • and suppose string contains ‘ab123cE234Fe’ then how should we handle it?

    • Stephen Yeadon
      August 4, 2015 4:12 pm

      nice solution

      Reply
  • SET NOCOUNT ON
    DECLARE @loop INT
    DECLARE @str VARCHAR(8000)
    DECLARE @strs VARCHAR(8000)
    SELECT @str = ‘ab123ce234fe’
    SET @loop = 0
    set @strs=”
    WHILE @loop < LEN(@str)
    BEGIN
    if(ASCII(SUBSTRING(@str,@loop+1,1)) between 48 and 57)
    begin
    set @strs=@strs+SUBSTRING(@str,@loop+1,1)
    end
    SET @loop = @loop + 1
    END
    print @strs

    Reply
  • Randeep Singh Matharu
    August 4, 2015 4:24 pm

    Below is another Solution, not neccessarily easier to write or especially good but great to see another alternative:

    Declare @strtest varchar(8000)
    Declare @StrFinal varchar(8000)
    Declare @start int

    select @strtest = ‘ab123ce234fe’
    set @StrFinal = ”
    set @start = 1

    while @start < 13

    if isnumeric(SUBSTRING(@strtest,@start,1))= 1

    Begin

    set @StrFinal = @StrFinal + SUBSTRING(@strtest,@start,1)
    set @start = @start + 1

    End

    else set @start = @start + 1

    select @StrFinal

    Reply
  • Scott Williams
    August 5, 2015 3:45 am

    CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
    RETURNS VARCHAR(1000)
    AS
    BEGIN
    WHILE PATINDEX(‘%[^0-9]%’, @strText) > 0
    BEGIN
    SET @strText = STUFF(@strText, PATINDEX(‘%[^0-9]%’, @strText), 1, ”)
    END
    RETURN @strText
    END

    Reply
  • Chris Munsell
    August 5, 2015 8:36 pm

    Mine is pretty close to Scott Williams… but there is a little difference.
    CREATE Function [dbo].[RemoveAlphaCharacters](@Temp VarChar(5000))
    Returns VarChar(1000)
    AS
    Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = ‘%[^0-9 ]%’
    While PatIndex(@KeepValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, ”)
    Return @Temp
    End

    Reply
  • If we had auxiliary table like, let’s say, numbers which contains numbers from 1 to
    then we would use the next (just for variety’s sake :)

    declare @str varchar (100) = ‘se345dsfg43dg435dfgh’;

    select ” + symbol
    from (
    select number, SUBSTRING (@str, number, 1) as [symbol]
    from dbo.numbers
    where number <= LEN (@str)
    and SUBSTRING (@str, number, 1) like '[0-9]'
    )
    data
    order by number
    for xml path ('')

    Reply
  • i have somethink like
    1)100w-123 i need output like 100-123
    2)1110A123w i need output like 11110123

    the above queries work for 2) case
    can anyone help on 1 st case

    Reply
    • Use the below code

      SET NOCOUNT ON
      DECLARE @loop INT
      DECLARE @str VARCHAR(8000)
      SELECT @str = ‘ab123ce234fe’
      SET @loop = 0
      WHILE @loop < 26 AND PATINDEX('%[a-z]%',@str) 0
      BEGIN
      SET @str = REPLACE(@str, CHAR(65 + @loop), ”)
      SET @loop = @loop + 1
      END
      SELECT @str

      Reply
  • Hello, I need help for solving the following problem: What is the appropriate way to replace:
    1 / 2 / 3 / 4 / 5 / 6 / 7 / 8 / 9 / 10 / 11 / 12 / 13 / 14 / 15 D / 16 D / 17 D / 18 D with
    text 1,text 2,text 3,text 4,text 5,text 6,text 7,text 8,text 9,text 10,text 11,text 12, text 13, another text 15,another text 16, another text 17, another text 18

    Thank you.

    Reply

Leave a Reply

Menu