Sunday, January 10, 2016

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

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Another idea


    declare @a varchar(120) , @b varchar(120) , @car char(1) = '.'
    Select @a ='octavio123Licea' , @b = @a

    while @car <> '' begin
    set @car = substring(@a , patindex('%[0-9]%',@a), 1)
    set @a = replace (@a , @Car,'')
    end
    set @car = '.'
    while @car <> '' begin
    set @car = substring(@b , patindex('%[A-z]%',@b), 1)
    set @b = replace (@b , @Car,'')
    end
    select @a as Str , @b as 'int'

    :)

    ReplyDelete
  3. It can be achieved with less code using PATINDEX. REfer below post

    http://www.itdeveloperzone.com/2012/01/get-numeric-value-from-string-in-sql.html

    ReplyDelete

Popular Posts