how to strip non alpha numeric characters in sql query

• gemini929

  Valued Member

  Points: 54

  More actions

  February 4, 2004 at 6:20 am

  #107524

  Hello all,

  I need to create a udf that will strip non alpha numeric characters (including spaces) from a given string.  Any idea how this can be done?

  Regards,

  Joshua

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  February 4, 2004 at 8:52 am

  #492763

  CREATE  FUNCTION dbo.test (@inputstring varchar(255))

  RETURNS varchar(255)

  AS

  BEGIN

  DECLARE @outputstring varchar(255)

  SET @outputstring = @inputstring

  DECLARE @pos int

  SET @pos = PATINDEX('%[^0-9A-Z]%',@outputstring)

  WHILE (@pos > 0)

  BEGIN

  SET @outputstring = STUFF(@outputstring,@pos,1,'')

  SET @pos = PATINDEX('%[^0-9A-Z]%',@outputstring)

  END

  RETURN @outputstring

  END

  Far away is close at hand in the images of elsewhere.
  Anon.

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 4, 2004 at 9:19 am

  #492766

  Just to add a little more to David Solution. Be careful with what you consider ALPHANUMERIC because some systems may need to use signs other characters and All you have to do is to include those on the PATINDEX pattern ex. '%[^0-9A-Z.-+]%'

  So, Know your DATA First!

   

  Just My $0.02

   

  
  * Noel