Search code examples
sqlsql-servertrim

SQL Server - RTRIM(LTRIM(column)) does not work

I am constantly selecting columns from a table after trimming them like the following:

SELECT TOP 1 RTRIM(LTRIM([UN_DataIN])) FROM [Names]

This is returning the name Fadi

SELECT TOP 1 RTRIM(LTRIM([UN_DataIN])), LEN(RTRIM(LTRIM([UN_DataIN]))) FROM [Names]

when I select the length of the trimmed column, I get back 10.

Which means RTRIM and LTRIM are not doing their jobs.

Is there an alternative to them?

Solution

  • UN_DataIN == 0x45062706470631062920292029202920292029202000

    So presuming Arabic your string ends with Unicode paragraph separators U+2029 and then a single whitespace all of which you need to remove;

    select rtrim(replace(UN_DataIN, nchar(0x2029), '')) + '!'

    ماهر!