0

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

Sujagni
  • 1
  • 1
  • 1
  • 1
  • Possible duplicate of [Oracle -- finding values with leading or trailing spaces](http://stackoverflow.com/questions/3223226/oracle-finding-values-with-leading-or-trailing-spaces) – Maciej Los Jun 20 '16 at 08:19
  • Here is quite interesting [tip](http://www.dba-oracle.com/t_find_leading_trailing_spaces_text_column.htm) – Maciej Los Jun 20 '16 at 08:21

1 Answers1

5

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);
User42
  • 970
  • 1
  • 16
  • 27
  • Hi Thanks. But i have thousand of records where trailing are not similar to above examples which i mentioned in my post. Trailing spaces may differ from record to record. There may b single, double, triple and so on. Please assist – Sujagni Jun 20 '16 at 07:02
  • I'm sorry, I don't fully understand. What do you mean by single, double, triple? And that trailing spaces may differ from record to record? In my example, one has 0, one has 7 and the other has 24 spaces. The query returns the ones with 7 and 24. – User42 Jun 20 '16 at 07:26
  • Sorry for confusion. In my table i have records which has 1,2,3,4,..........100 trailing spaces. and i need to find these type of records. – Sujagni Jun 20 '16 at 07:31
  • 1
    Do you want to find all of them at once, or only the ones which have exactly `x` trailing spaces? Let's say `x = 4`. If you want to find all, the above `WHERE LENGTH(RTRIM(text)) != LENGTH(text)` should do. If you want to find the ones with exactly `x = 4` trailing spaces, try `WHERE LENGTH(RTRIM(text)) + 4 = LENGTH(text)` – User42 Jun 20 '16 at 07:35
  • Hi. Yes i want find all of them at once. I used "LENGTH(RTRIM(PARTY_NAME)) ! = LENGTH(PARTY_NAME)" to find out all of them, however it is returning the records on with single trailing space. Please advice. – Sujagni Jun 20 '16 at 09:17
  • I am running these scripts on TOAD ORACLE – Sujagni Jun 20 '16 at 09:52
  • I updated my answer (see the part about `HORIZONTAL TAB`, `LINE FEED`, and `CARRIAGE RETURN`). Maybe this helps? – User42 Jun 20 '16 at 10:01
  • Thanks. I will check this one. and yes i have horizontal tabs in those records. Thanks agian. Will let you know. – Sujagni Jun 20 '16 at 10:07