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
