Replacing set of character in sql query

Question

How do i replace set of character in a table?

ColumnA
%%1234UIT
!!1234TTT
678@@PPP

Currently I am using

`replace(replace(replace([BLOCK_NAME],'%%',''),'!!',''),'@@',''),1,LEN(BLOCK_NAME)-1`
OR
replace(replace(replace(substring([BLOCK_NAME],1,LEN(BLOCK_NAME)-1),'**',''),'##',''),'$$','')

Expected OUTPUT:
1234UIT
123TTT
678PPP

What's the expected output? The expression you've shown is incomplete - it looks like you've stripped out maybe a `SUBSTRING` call? Because your expression currently is `,number,number` — Damien_The_Unbeliever, Oct 18 '13 at 06:55
@Damien_The_Unbeliever you are right. I escaped substring. I just want to know if there is single function doing all the replacing. — kinkajou, Oct 18 '13 at 07:03

score 1 · Answer 1 · answered Oct 18 '13 at 07:06

This should work for your problem, it removes all non numeric characters from the record. You can create a sql function to be called from your query.

WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
    SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText

score 1 · Answer 2 · edited May 23 '17 at 10:25

Please refer how to remove non-numeric/non-alphanumeric characters from string

CREATE FUNCTION [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^a-z0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^a-z0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

select data using the created function

SELECT 
    dbo.fnRemoveNonNumericCharacters(BLOCK_NAME)
FROM 
    YourTable

OR

;WITH T as(
  SELECT STUFF(BLOCK_NAME, PATINDEX('%[^a-z0-9]%', BLOCK_NAME), 1, '') BLOCK_NAME from YourTable
  UNION ALL
  SELECT STUFF(BLOCK_NAME, PATINDEX('%[^a-z0-9]%', BLOCK_NAME), 1, '') BLOCK_NAME from T where PATINDEX('%[^a-z0-9]%', BLOCK_NAME) > 0
)
select 
    * 
from 
    T 
where PATINDEX('%[^a-z0-9]%', BLOCK_NAME) = 0

I prefer the second method using CTE without a user-defined function.

score 1 · Answer 3 · answered Oct 18 '13 at 07:21

You can create a function as:

CREATE FUNCTION [dbo].[fn_RemoveCharacters]
(
    @Str NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @Str) > 0
        SET @Str = Stuff(@Str, PatIndex(@MatchExpression, @Str), 1, '')

    RETURN @Str

END

Go

and then call it as:

SELECT [dbo].[fn_RemoveCharacters](ColumnA, '%%!!@@') as ColumnA
from table1

Hope this helps!!!

Replacing set of character in sql query

3 Answers3