Post reply

Replace cr/lf not working with Japanese characters

  • January 21, 2020 at 2:54 pm

    Go to Answer

    #3717053

    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 4 years, 10 months ago by  Keith DuAime. Reason: question marks showed when in code
    • This topic was modified 4 years, 10 months ago by  Keith DuAime. Reason: question marks showed when in code

  • January 21, 2020 at 9:56 pm

    Answer

    #3717185

    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.

    SELECTSampleWord,
    [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)

     

  • January 22, 2020 at 1:03 pm

    #3717355

    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 2 (of 2 total)

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