Search Special characters

• Lowell

  SSC Guru

  Points: 323741

  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: 182951

  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