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

Leave a Reply

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