Post reply

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

  • 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.

  • 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]

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply