Identify rows containing non-English characters within a unicode nvarchar.

• DBANewbie

  Hall of Fame

  Points: 3095

  More actions

  February 10, 2011 at 7:40 am

  #388832

  Hi All,

  I am trying to create query which will return all data from a table where the entry in a specific column contains characters from foreign languages.

  The column in question is stored as unicode and i have noticed the odd occassion where the text contains foreign characters.

  I want to run a query to capture every row that contains these foreign characters and i am wondering if there is a way to do it other then using a like clause and writing out each character in question.

  Apologies if i haven't explained this very well; if you need any further info please let me know.

  Thanks.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 10, 2011 at 8:59 am

  #1285270

  Here is a general way to detect a character whose value is greater than the "Englishl" character set. Note the code is more extensive than it should be for your requirement, but is meant to show you a method of doing what you require. It should be modified to exit the checking at the discovery of the first character that has a value greater than 127. Note that the basic code was taken from BOL.

  The value for the "Å" is 197

  DECLARE @string nchar(9)

  SET @position = 1

  SET @string = 'New Moon' + CHAR(197)

  PRINT @string --Prints New MoonÅ

  WHILE @position <= LEN(@string)

  BEGIN

  SELECT ASCII(SUBSTRING(@string, @position, 1)),@position AS 'position'

  ,CHAR(ASCII(SUBSTRING(@string, @position, 1)))

  SET @position = @position + 1

  END

  DECLARE @position int

  DECLARE @string nchar(9)

  SET @position = 1

  SET @string = 'New Moon' + CHAR(197)

  PRINT @string --Prints New MoonÅ

  WHILE @position <= LEN(@string) --DATALENGTH(@string)

  BEGIN

  SELECT ASCII(SUBSTRING(@string, @position, 1)),@position AS 'position'

  ,CHAR(ASCII(SUBSTRING(@string, @position, 1)))

  SET @position = @position + 1

  END

  You should also check BOL for "Working with Collations "

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• DBANewbie

  Hall of Fame

  Points: 3095

  More actions

  February 10, 2011 at 9:27 am

  #1285288

  That's great, thanks very much for your help. I had looked online but my google searches must have been too vague to point me to the relevent BOL article.