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

 > 

  • Question

  • Question
    Sign in to vote
    1
    Sign in to vote 
    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

  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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