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