ltrim(rtrim(x)) leave blanks on rtl content - anyone knows on a work around?

Question

i have a table [Company] with a column [Address3] defined as varchar(50) i can not control the values entered into that table - but i need to extract the values without leading and trailing spaces. i perform the following query:

SELECT DISTINCT RTRIM(LTRIM([Address3])) Address3 FROM [Company] ORDER BY Address3

the column contain both rtl and ltr values most of the data retrieved is retrieved correctly - but SOME (not all) RTL values are returned with leading and or trailing spaces

i attempted to perform the following query:

SELECT DISTINCT ltrim(rTRIM(ltrim(rTRIM([Address3])))) c, ltrim(rTRIM([Address3])) b, [Address3] a, rtrim(LTRIM([Address3])) Address3 FROM [Company] ORDER BY Address3

but it returned the same problem on all columns - anyone has any idea what could cause it?

Answer 1

The rows that return with extraneous spaces might have a kind of space or invisible character the trim functions don't know about. The documentation doesn't even mention what is considered "a blank" (pretty damn sloppy if you ask me). Try taking one of those rows and looking at the characters one by one to see what character they are.

Answer 2

since you are using varchar, just do this to get the ascii code of all the bad characters

--identify the bad character
SELECT 
    COUNT(*) AS CountOf
        ,'>'+RIGHT(LTRIM(RTRIM(Address3)),1)+'<' AS LastChar_Display
        ,ASCII(RIGHT(LTRIM(RTRIM(Address3)),1))  AS LastChar_ASCII
    FROM Company 
    GROUP BY RIGHT(LTRIM(RTRIM(Address3)),1)
    ORDER BY 3 ASC

do a one time fix to data to remove the bogus character, where xxxx is the ASCII value identified in the previous select:

--only one bad character found in previous query
UPDATE Company
    SET Address3=REPLACE(Address3,CHAR(xxxx),'')

--multiple different bad characters found by previous query
UPDATE Company
    SET Address3=REPLACE(REPLACE(Address3,CHAR(xxxx1),''),char(xxxx2),'')

if you have bogus chars in your data remove them from the data and not each time you select the data. you WILL have to add this REPLACE logic to all INSERTS and UPDATES on this column, to keep any new data from having the bogus characters.

If you can't alter the data, you can just select it this way:

SELECT
    LTRIM(RTRIM(REPLACE(Address3,CHAR(xxxx),'')))
    ,LTRIM(RTRIM(REPLACE(REPLACE(Address3,CHAR(xxxx1),''),char(xxxx2),'')))
...