Saturday, January 27, 2024

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog