How to Remove the Special Characters from a Column in SQL Server

How to use PATINDEX Function to Remove the Special Characters from a Column in SQL Server
Scenario:

Suppose we have a sample data with Special Charaters as shown below:

1!2@3#4$5%6^7&8*9(0)_+,<.€25>/?\|

ABC$123.45abc|€987.45

$123.45

Gold@$916.75

We need to remove the Special Characters like !@#$%^&*()_+,<€>/?\| , and clean the data as per below:

1234567890.25

ABC123.45abc987.45

123.45

Gold916.75

We can achieve this Scenario, by using a Function created with use of PATINDEX and STUFF Functions as explained below:

/* Create a Function to Remove the Special Chars (except dot.) */

CREATE FUNCTION  [dbo].[fn_RemoveSpecialChars](@StrValue VARCHAR(250))

RETURNS VARCHAR(250)

AS

BEGIN

 DECLARE @SpecialStr VARCHAR(250) = '%[^0-9.Aa-Zz]%'

 WHILE PATINDEX(@SpecialStr,@StrValue)>0

                 SET @StrValue = STUFF(@StrValue,PATINDEX(@SpecialStr,@StrValue),1,'')

                 Return @StrValue

 END

GO

Please Note:
The above Function keeps '%[^0-9.Aa-Zz]%', all Numbers (0-9), Text (Aa-Zz) and removes all Special Characters except .dot, as it is expected for the Numerical values.

Now lets create a Table with the sample data for testing.

/* Check and Drop the table if it already exists: */

IF OBJECT_ID('[dbo].[tbl_Sample]','U') IS NOT NULL

    BEGIN

        DROP  TABLE [dbo].[tbl_Sample]

    END

ELSE

    BEGIN

       PRINT 'The target Object unable to Drop as it is Not Found'

    END

GO

/* Create the sample table */

CREATE TABLE [dbo].[tbl_Sample](

    [FieldID] INT NULL,

    [FieldValue] [varchar](255) NOT NULL

) ON [PRIMARY]

GO

/* Insert the sample data into the table */

INSERT INTO  [dbo].[tbl_Sample]

SELECT 1, '1!2@3#4$5%6^7&8*9(0)_+,<.€25>/?\|' UNION ALL

SELECT 2, 'ABC$123.45abc|€987.45' UNION ALL

SELECT 3, '1234567890' UNION ALL

SELECT 4, '$123.45' UNION ALL

SELECT 5, 'Gold@$916.75' UNION ALL

SELECT 6, 'Elephant' UNION ALL

SELECT 7,'€987.65' UNION ALL

SELECT 8,'Silver@€75.25' UNION ALL

SELECT 9,'€1234' UNION ALL

SELECT 10, 'Knight' ;

GO

Select * From [dbo].[tbl_Sample]

GO

/* Clean the Special Characters from the FieldValue: */

SELECT *,

    PATINDEX('%[$€]%', FieldValue ) AS PatCurPos,

    PATINDEX('%[Aa-Zz]%', FieldValue ) AS PatStrPos,

    PATINDEX('%[0-9]%', FieldValue ) AS PatNumPos,

    dbo.fn_RemoveSpecialChars(FieldValue) AS CleanField,

    CASE  

        WHEN (PATINDEX('%[Aa-Zz]%', dbo.fn_RemoveSpecialChars(FieldValue) )>0)

            THEN NULL

        WHEN (PATINDEX('%[0-9]%', FieldValue )=0 OR FieldValue IS NULL)

            THEN NULL          

        WHEN (PATINDEX('%[$€]%', FieldValue )>0 AND PATINDEX('%[0-9]%', FieldValue )>0)

        THEN CAST(dbo.fn_RemoveSpecialChars(FieldValue) AS DECIMAL(18,4))

    ELSE CAST(FieldValue AS DECIMAL(18,4)) END NumField

FROM [dbo].[tbl_Sample]

Notes:

The [PatCurPos] returns the starting position of Currency Pattern '%[$€]%' in the FieldValue.
The [PatStrPos] returns the starting position of String Pattern %[Aa-Zz]% in the FieldValue.
The [PatNumPos] returns the starting position of Numeric Pattern '%[0-9]%' in the FieldValue.
The [CleanFeild] returns the FieldValue after cleansing the Special Characters.

/* Check and Drop the Function if it already exists:*/

IF OBJECT_ID(N'[dbo].[fn_RemoveSpecialChars]', N'FN') IS NOT NULL

    DROP FUNCTION [dbo].[fn_RemoveSpecialChars]

GO

--OR--

/*

IF EXISTS (

    SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[fn_RemoveSpecialChars]')

        AND xtype IN (N'FN', N'IF', N'TF')

    )

DROP FUNCTION [dbo].[fn_RemoveSpecialChars]

GO

Function Types:

FN = Scalar Function

IF = Inlined Table Function

TF = Table Function

*/

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------