CODEWITHSUNDEEP
sqloracle-databasetrailing
Sujagni
Sujagni

Reputation: 1

Trailing spaces plsql

I need to find out the records with trailing spaces. I tried to build a query by using Length(rtrim), however it is not returning the required records.

e.g. 

abc "
abc       "
abc                                                               "

Please assist

Upvotes: 0

Views: 128

Answers (1)

User42
User42

Reputation: 965

I think you were on the right track. The following example would return the second and third text, but not the first. 

SELECT * FROM
    (SELECT 'abc' AS text FROM DUAL
     UNION ALL
     SELECT 'abc       ' AS text FROM DUAL
     UNION ALL
     SELECT 'abc                        ' AS text FROM DUAL)
WHERE LENGTH(RTRIM(text)) != LENGTH(text);

Or for a table YOURTABLE with the column YOURCOLUMN containing the text with maybe trailing spaces:

SELECT * FROM
YOURTABLE
WHERE LENGTH(RTRIM(YOURCOLUMN)) != LENGTH(YOURCOLUMN);

If you also have HORIZONTAL TAB, LINE FEED, or CARRIAGE RETURN you can use the TRANSLATE function to treat these as normal spaces:

SELECT * FROM
    (SELECT 'abc' AS text FROM DUAL
     UNION ALL
     SELECT 'abc' || CHR(09) AS text FROM DUAL
     UNION ALL
     SELECT 'abc' || CHR(10) AS text FROM DUAL
     UNION ALL
     SELECT 'abc' || CHR(13) AS text FROM DUAL
     UNION ALL
     SELECT 'abc           ' AS text FROM DUAL)
WHERE LENGTH(RTRIM(TRANSLATE(text, CHR(09) || CHR(10) || CHR(13), ' '))) != LENGTH(text);

Upvotes: 5

Related Questions