locked
Stripping all characters other than alphabets and numbers from a string RRS feed

  • Question

  • DECLARE @pos INT
    DECLARE @myString VARCHAR(20)
    SET @myString='se+*ar%c&h^da#tab~se'
    SELECT @myString
    SET @pos = PATINDEX('%[^a-z0-9_]%',@myString)
    SELECT @pos
    
    -- sets @pos to the position in @myString of the first character
    -- not (^) equal to a letter (a-z), a digit (0-9) or an underscore
    -- or sets @pos to 0 if no such character exists in @myString (in which
    -- case you are done)
     
    WHILE @pos > 0 
    BEGIN 
      SET @myString = STUFF(@myString,@pos,1,'')
      -- overwrites the non-alphanumeric character at position @pos with ''
      SET @pos = PATINDEX('%[^a-z0-9_]%',@myString)
      -- looks for the next non-alphanumeric character
    END
     
    SELECT @myString

    I am basically looking for a best way to strip all characters other than alphabets and number from a string. 

    Please advise if this method which I figured out from Google online is  the best method ?

    Also string operation like this should be performed at the SSIS level or SQL level ? Given a context where in I have column in a table which I am pulling using sql and dumping using SSIS in CSV?







    • Edited by MLTC Monday, January 25, 2016 5:07 PM
    Monday, January 25, 2016 4:31 PM

Answers

All replies

  • See this blog post http://beyondrelational.com/modules/2/blogs/78/posts/11141/remove-bad-characters-from-a-string.aspx and comments to it.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by FLauffer Monday, January 25, 2016 4:44 PM
    • Marked as answer by MLTC Sunday, January 31, 2016 11:54 PM
    Monday, January 25, 2016 4:38 PM
  • Thanks .

    Also string operation like this should be performed at the SSIS level or SQL level ? Given a context where in I have column in a table which I am pulling using sql and dumping using SSIS in CSV?

    I think I need to create a function and call at the db sql level and then pass the result to SSIS?




    • Edited by MLTC Monday, January 25, 2016 5:07 PM
    Monday, January 25, 2016 5:02 PM
  • It could be CLR function and it can be performed at the SSIS level. You may have better luck asking in that forum as well.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 25, 2016 5:18 PM
  • Try this one on:

    CREATE FUNCTION [dbo].[onlyAlphaNumeric](@inputString NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    DECLARE @outputString NVARCHAR(MAX) = ''
     WHILE LEN(@inputString) > 0
     BEGIN
      SET @outputString = @outputString + CASE WHEN ASCII(LEFT(@inputString,1)) BETWEEN 48 AND 57 OR ASCII(LEFT(@inputString,1)) BETWEEN 65 AND 90 OR ASCII(LEFT(@inputString,1)) BETWEEN 97 AND 122 THEN LEFT(@inputString,1) ELSE '' END
      SET @inputString = RIGHT(@inputString,LEN(@inputString)-1)
     END
     RETURN @outputString
    END
    
    It's not going to perform well, but it will do the job.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, January 25, 2016 5:25 PM