Search Special characters

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  July 14, 2014 at 6:49 am

  #309900

  Hi All,

  I am using SQL Server 2008. In one of my table, one column has values like

  "MFY RLHH CSQÉ"

  "Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"

  So to fetch the data having only special characters in it, I used below query

  Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [http://unicode-table.com/en/#control-character]

  So can anyone guide me?

  Thanks in advance,

  -- Mahesh

  MH-09-AM-8694

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 14, 2014 at 9:51 am

  #1729182

  i think you just need to switch to a binary collation, otherwise accented letters like àâáãäå are all the same as 'a', right?

  Select * From [Table] Where [Column] Like '%[^0-9a-zA-Z ]%' COLLATE Latin1_General_BIN

  edit: grabbing Eirikur Eiriksson's fine example as proof:

  DECLARE @TEST_STR TABLE

  (

  TEST_STRING NVARCHAR(255) NOT NULL

  );

  INSERT INTO @TEST_STR(TEST_STRING)

  VALUES

  (N'MFY RLHH CSQÉ')

  ,(N'Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw')

  ,(N'')

  Select * From @TEST_STR Where TEST_STRING Like '%[^0-9a-zA-Z ]%' COLLATE Latin1_General_BIN

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  July 14, 2014 at 10:14 am

  #1729189

  Quick suggestions, you could use the UNICODE function, something like this

  😎

  USE tempdb;

  GO

  DECLARE @TEST_STR TABLE

  (

  TEST_STRING NVARCHAR(255) NOT NULL

  );

  INSERT INTO @TEST_STR(TEST_STRING)

  VALUES

  (N'MFY RLHH CSQÉ')

  ,(N'Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw')

  ,(N'')

  DECLARE @RANGE_FROM INT = 513;

  DECLARE @RANGE_TO INT = 1806;

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

  SELECT

  TS.TEST_STRING

  ,COUNT(CASE WHEN UNICODE(SUBSTRING(TS.TEST_STRING,NM.N,1)) BETWEEN @RANGE_FROM AND @RANGE_TO THEN 0 ELSE 1 END)

  FROM @TEST_STR TS

  OUTER APPLY

  (SELECT TOP(LEN(TS.TEST_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9) AS NM(N)

  GROUP BY TS.TEST_STRING