SQL - Remove non-numeric characters from a string

SQL - Remove expected characters from a string

In our day to day activities, we need to remove non-numeric, numeric or sometimes need to remove special characters from the string. We understand that there are many situations where you need to do a lot of string manipulation using T-SQL. The true fact is that many things which ideally should be done via SQLCLR. To handle this situation, we need an innovative solution which will be capable to return the expected result.

We are going to create a User defined SQL function which will be return the expected result based on the passed arguments. To meet the requirement, this function will perform the action based on the two parameters such as -

1. First parameter will take the requested string to perform the action and,
2. Another parameter will tell the action type to function.

There are lot of methods to get the expected result but we will use the ASCII value to get the expected result sets because every character position already defined in the ASCII.

/* -- Removes special characters from a string value. -- All characters except 0-9, a-z and A-Z are removed and -- the remaining characters are returned. @inpActionType = 1 : Remove Special Characters @inpActionType = 2 : Remove all characters @inpActionType = 3 : Remove integer values */ CREATE FUNCTION dbo.fun_GetString ( ----- Input String Argument on which action will be performed @inpString VARCHAR(50), ----- Input Action Type Argument @inpActionType int ) ---- Set return type RETURNS VARCHAR(50)    BEGIN    ---- check the nullables    IF @inpString IS NULL       RETURN NULL    ----- declare local variable with default blank value    DECLARE @outResult VARCHAR(50) = ''    ----- declare local variable to get the length of the string    DECLARE @intLength INT = 0, @intStartingPoint int=1    ----- Set the length into the variable    SET @intLength = LEN(@inpString)       ---- check the length with the default starting point    WHILE @intStartingPoint <= @intLength       BEGIN       ---- declare local variable to get the ASCII value       DECLARE @ASCII INT                     ---- Set the ASCII Value into the variable       SET @ASCII = ASCII(SUBSTRING(@inpString, @intStartingPoint, 1))                     ----- Remove Special Characters                     IF @inpActionType = 1                     BEGIN       IF @ASCII BETWEEN 48 AND 57 OR @ASCII BETWEEN 65 AND 90 OR @ASCII BETWEEN 97 AND 122          SET @outResult = @outResult + CHAR(@ASCII)       END                     ---- Remove all characters                     ---- Contains only numeric ASCII Values                     IF @inpActionType = 2                     BEGIN                     IF @ASCII BETWEEN 48 AND 57          SET @outResult = @outResult + CHAR(@ASCII)                     END                     ---- Remove integer values                     ---- Contains only character ASCII Values                     IF @inpActionType = 3                     BEGIN                     IF @ASCII BETWEEN 65 AND 90 OR @ASCII BETWEEN 97 AND 122          SET @outResult = @outResult + CHAR(@ASCII)                     END                     ---- set the next length position       SET @intStartingPoint = @intStartingPoint + 1       END    ---- Return Null when output result string lenth is 0    IF LEN(@outResult) = 0       RETURN NULL    ---- Return Output result    RETURN @outResult    END    ----- Execute the function with arguments    declare @inpRealString Varchar(50)='Ryan$010Arjun#123%India'    select    ----- Result without special characters    NoSpecialChar=dbo.fun_GetString (@inpRealString,1),    ----- Result for numeric only    OnlyNumeric=dbo.fun_GetString (@inpRealString,2),    ----- Result for characters only    OnlyString=dbo.fun_GetString (@inpRealString,3) NoSpecialChar OnlyNumeric OnlyString Ryan010Arjun123India 10123 RyanArjunIndia

Conclusion
There’s SQL replace and pattern functions, but they only work once on a string. By using the above function, we can get the expected result based on  passed arguments. 

Learn more on another feature of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Views
6. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
7. SQL - Merge Statement
8. SQL - Functions
9. SQL - Cursors
10. SQL - Logical IIF() Function
11. SQL - ISNULL Function
12. SQL - CONCAT() function
13. SQL - Stuff() Function