CODEWITHSUNDEEP
sqloracle-database
dee
dee

Reputation: 609

How can I use RTRIM or REPLACE when I know the length I want to trim but not what it may contain?

I need to RTRIM the last 7 characters from a result set in an Oracle query. These 7 chars can be anything; spaces, alpha numeric etc... and I don't know the exact length of any value.

So for example I'd like to run something like this

 SELECT RTRIM (COl_A, (SELECT LENGTH (COL_A)-7) FROM TABLE_ONE;

or a replace equivalent

SELECT REPLACE(COL_A, (SELECT LENGTH (COL_A)-7 FROM TABLE_ONE),'');

Do I need to do something with SUBSTRING maybe?

I know how to remove/replace specific chars but I'm having trouble when dealing with unknown chars. I've seen a few examples of similar problems but they seem unnecessarily complicated... or does this require a more in depth solution than I think it should?

As always thanks in advance for advice or hints.

Upvotes: 0

Views: 606

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Actually, the correct solution is:

select substr(col_a, 1, (case when length(col_a) < 7 then 0 else length(col_a) - 7 end) from table_one

To be general, you would want to take into account what happens when the length is less than 7.

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116140

You are in search of the substr function.

select substr(col_a, 1, length(col_a) - 7) from table_one

Upvotes: 7

Related Questions