Need SQL queries please for string patterns

• challapal

  SSC Veteran

  Points: 209

  More actions

  July 9, 2012 at 10:09 am

  #1510425

  CREATE FUNCTION dbo.RemoveSpecialChars1 (@pSomeString VARCHAR(8000))

  RETURNS VARCHAR(8000) WITH SCHEMABINDING AS

  BEGIN

  --===== Delete all special characters except spaces.

  WHILE PATINDEX('%[^a-zA-Z0-9 ]%',@pSomeString COLLATE Latin1_General_BIN) > 0

  SET @pSomeString = STUFF(@pSomeString,PATINDEX('%[^a-zA-Z0-9 ]%',@pSomeString COLLATE Latin1_General_BIN),1,'');

  --===== Convert empty strings to NULL, replace multiple adjacent spaces with just one, and return the function value.

  RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NULLIF(@pSomeString,''),' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');

  END

  SELECT dbo.RemoveSpecialChars1('This string contains special chracters:/ Which * & we % need @ to #remove');--Working for this

  SELECT dbo.RemoveSpecialChars ('(?)??????????')---not working for this ,i dont want fucntion to consider chinese,doublebyte,japanese,korean any otherlang as speial char , this function considers those as special char and replacing them as my fucntion does too

  can you please let me know how to change above fucntion to not consider chinese,doublebyte,japanese,korean,russian any other non english as special char

  and consider only special char as special char