Replace cr/lf not working with Japanese characters

  • Keith DuAime

    Ten Centuries

    Points: 1121

    I tried many different ways and built a function to basically go character by character checking the unicode values of 13,10 but why doesn't this simply work. tried char(13/10), nchar(13/10), etc...

    SELECT REPLACE(REPLACE(txt,NCHAR(0x0D),''),NCHAR(0x0A),'') broke, REPLACE(REPLACE(wrks,NCHAR(0x0D),''),NCHAR(0x0A),'') wrks

    FROM (

    SELECT NCHAR(65404) + NCHAR(13) + NCHAR(10) + NCHAR(65438) + NCHAR(65412) txt,

    N'test

    value' wrks

    ) data

     

    • This topic was modified 8 months ago by  Keith DuAime. Reason: question marks showed when in code
    • This topic was modified 8 months ago by  Keith DuAime. Reason: question marks showed when in code
  • fahey.jonathan

    Hall of Fame

    Points: 3567

    I believe it has to do with collation, based on his article: https://dba.stackexchange.com/questions/208414/why-isnt-unicode-character-replaced-in-some-cases.

    SELECT	SampleWord,
    [No_Collate] = CAST(REPLACE(REPLACE(SampleWord, NCHAR(0x0D), ''), NCHAR(0x0A), '') AS NVARCHAR(50)),
    [With_Collate] = CAST(REPLACE(REPLACE(SampleWord COLLATE Latin1_General_100_BIN2, NCHAR(0x0D), ''), NCHAR(0x0A), '') AS NVARCHAR(50))
    FROM (VALUES
    ('test

    value'),
    ('test' + NCHAR(13) + NCHAR(10) + 'valu2'),
    (NCHAR(65404) + NCHAR(13) + NCHAR(10) + NCHAR(65438) + NCHAR(65412))
    ) v (SampleWord)

     

  • Keith DuAime

    Ten Centuries

    Points: 1121

    Does the trick, thank you. Oddly enough I was using collate on another column when using charindex for another field and must have just thought replace was different.

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

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