SQL Function to remove characters from string

One of the most common questions over forums is a SQL function to remove characters from string. The function for the same is given below.

-- Method 1: Loop
IF(Object_id('dbo.fn_removecharactersfromstring')) is not null
drop function dbo.fn_removecharactersfromstring
GO
CREATE FUNCTION fn_removecharactersfromstring 
(
	-- Add the parameters for the function here
	@string nvarchar(max) 
	 
)
RETURNS nvarchar(max)
AS
BEGIN
	-- loop untill all characters are replaced
	WHILE PATINDEX('%[^0-9]%',@string) <> 0
	BEGIN
    -- remove characters with empty space
    SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'')
	END
	
	RETURN @string
END
GO

SELECT dbo.fn_removecharactersfromstring('0123-abc-456-def-789')

The above function uses while to loop through the string until all the characters are replaced/removed by empty space. What left is the rest of the string i.e. numbers.

-- Method 2: Set based
IF(Object_id('dbo.fn_removecharactersfromstring')) is not null
drop function dbo.fn_removecharactersfromstring
GO
CREATE FUNCTION fn_removecharactersfromstring 
(
	-- Add the parameters for the function here
	@string nvarchar(max) 
	 
)
RETURNS nvarchar(max)
AS
BEGIN

;WITH T1(number) AS (SELECT 1 UNION ALL SELECT 1),
T2(number)  AS (SELECT 1 FROM T1 AS a cross join T1 as b),
T3(number)  AS (SELECT 1 FROM T2 AS a cross join T2 as b),
T4(number)  AS (SELECT 1 FROM T3 AS a cross join T3 as b),
Nums(number) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
from T4)
SELECT @string=STUFF( 
(SELECT   '' + SUBSTRING(@string,Nums.number,1)
FROM Nums
WHERE PATINDEX('%[0-9]%',SUBSTRING(@String,Nums.number,1))>0 
FOR XML PATH('')),1,0,'')

Return @string
END

The above query uses tally table to split the string into individual rows and check and then concatenate numeric rows into a string.

 

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *