Ignore spaces in substring SQL/Oracle

Question

I'm trying to substring the last 3 digits from a column. Problem is, some of them have a random space at the end, which then only returns 2 of the 3 needed values from the column. Right now I have:

    SELECT substr(infovalue, -3)
    FROM TABLE
    WHERE INFOCODE = 555

How can I get my statement to ignore the space at the end?

thanks!

Does the space need to be there?

Jake
– Jake

2014-08-28 18:37:41 +00:00
Commented Aug 28, 2014 at 18:37 — Jake
– Jake, Commented Aug 28, 2014 at 18:37
What RDBMS (Versions of trim may work here)

xQbert
– xQbert

2014-08-28 18:38:58 +00:00
Commented Aug 28, 2014 at 18:38 — xQbert
– xQbert, Commented Aug 28, 2014 at 18:38

Code Different · Accepted Answer · 2014-08-28 18:37:55Z

2

I guess you are in Oracle. Apply an rtrim to remove all trailing spaces

SELECT substr(rtrim(infovalue), -3)
FROM TABLE
WHERE INFOCODE = 555

And please specify the vendor & product version for all your questions.

answered Aug 28, 2014 at 18:37

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DEwok Over a year ago

Awesome! This works...I will specify vendor and product from now on!

Collectives™ on Stack Overflow

Ignore spaces in substring SQL/Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related