SQL Function to Remove Special Characters and Convert to Multiple Columns by using ParseName Function

In some cases, we may need to remove special characters from a input string such as title or description fields. Sql server does not have build-in function to do that. We have to create our own user-defined function. This function removes any special character from the given string.

CREATE FUNCTION [dbo].[ufn_RemoveSpecialChars]

(

    @Input VARCHAR(MAX)

)

RETURNS VARCHAR(MAX) 

BEGIN

    

   DECLARE @Output VARCHAR(MAX  )

    

   IF (ISNULL(@Input,'')='')

      SET @Output = @Input

   ELSE

   BEGIN

    

    DECLARE @Len INT

    DECLARE @Counter INT

    DECLARE @CharCode INT

         

    SET @Output = ''

    SET @Len = LEN(@Input)

    

   SET @Counter = 1

    

       WHILE @Counter <= @Len

       BEGIN

           

          SET @CharCode = ASCII(SUBSTRING(@Input, @Counter, 1))

          IF @CharCode=32 OR @CharCode BETWEEN 48 and 57 OR @CharCode BETWEEN 65 AND 90 OR @CharCode BETWEEN 97 AND 122 

             SET @Output = @Output + CHAR(@CharCode)

              

          SET @Counter = @Counter + 1

           

       END

          

   END

       

   RETURN @Output

    

END










Sample Usage

SELECT dbo.[ufn_RemoveSpecialChars]('This^ is $- sample ~text.#&*')

--Output : This is  sample text







Note

If this doesn't meet your requirement, just alter a little bit. Find the ascii code of character what you want to remove. You can check the char code with the code like follow.

?

1 2 3 4

SELECT ASCII('%') -- Output : 37 SELECT ASCII('~') -- Output : 126

--Final Query 
--Used function to remove special characters.
--Used ParseName builtin function to split the FullName depending upon ''( space).
--Wrote Case Statement to adjust middle name to last name where there is no last name.

;WITH CTE
     AS (SELECT Reverse(Parsename(Replace(

Reverse(dbo.[Ufn_removespecialchars](FullName)), ' ', '.'), 1)) AS [FirstName],
                Reverse(Parsename(Replace(Reverse(dbo.[Ufn_removespecialchars](FullName)), ' ', '.'), 2)) AS [MiddleName],
                Reverse(Parsename(Replace(Reverse(dbo.[Ufn_removespecialchars](FullName)), ' ', '.'), 3)) AS [LastName]
         FROM   dbo.ColSplit)
SELECT FirstName,
       CASE
         WHEN LastName IS NULL THEN NULL
         ELSE MiddleName
       END AS MiddleName,
       CASE
         WHEN LastName IS NULL
              AND MiddleName IS NOT NULL THEN MiddleName
         ELSE LastName
       END AS LastName
FROM   CTE

The above query can be changed according to the requirement. We can also write a user function to split string string into multiple columns. Here we were required to split FirstName,MiddleName and LastName and that can be handled with built in function.