Select where Right (ID, 2) = ('00','01')


I would like to add a where clause that if the 2 last chars in the field ID = '00' or '01' .

That way, i will get all the PO ID that finish with 00 or 01.

How can i do that?

thanks for your help.


Open in new window

LVL 11
Who is Participating?
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
... Where Substr(SOURCE_ID, -2) IN ('21','24','03','60','67','34','57')
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
But note that renders any index unusuable, so it might introduce performance penalities.
PortletPaulConnect With a Mentor freelancerCommented:
What type of field is "ID"? Integer?

If it is integer then you would have to convert to string first, then use: SUBSTR(STRING_ID, -2) IN ('00','01')

But also note that using functions like this is not great for performance as indexes won't be used - unless you also create a function based index to suit.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Wilder1626Author Commented:
The field that i have is a generic number with 12 numbers. Ex: '201412161823'

let me give it a try.

Alexander Eßer [Alex140181]Software DeveloperCommented:
Another option could be to create a virtual column with the SUBSTR... and then create an index upon that new virtual column ;-)
johnsoneSenior Oracle DBACommented:
If the field is a number datatype, there is no need to convert it to a string.  The SUBSTR function call will do an implicit conversion for you.

However, be aware that if you are looking for 00 or 01 and the field contains a single digit number (i.e. 1) it will not be found because there is no leading 0.  If a single digit could happen and you want to select that row, you would have to do the conversion to character yourself and account for adding leading 0s.
Alexander Eßer [Alex140181]Software DeveloperCommented:
to do the conversion to character yourself and account for adding leading 0s
-> using LPAD function

The SUBSTR function call will do an implicit conversion for you.
-> Yes, but you definitely should use a conversion function (TO_CHAR) whenever using numeric values in "string-oriented" functions! It's far more "readable" and imho a better/proper coding style/pattern. Implicit conversions may cause weird problems which in trun might be hard to find...
johnsoneSenior Oracle DBACommented:
If you need to add leading 0s and you want to do an explicit conversion, why use both TO_CHAR and LPAD?  TO_CHAR is capable of the padding required in this scenario without an additional function call.

While in most cases I would use explicit conversion, on number to string, there really isn't much environmental factors that could change the outcome.  Date to string absolutely.  An implicit conversion would tend to be a little faster performance wise as well.
Alexander Eßer [Alex140181]Software DeveloperCommented:
another nice link about padding in Oracle:
I'm not sure what you mean by the field being a "generic number". Is it a varchar2 datatype or a number datatype. If characters, the substring method suggested (i.e. substr(field,-2) in ('00',01') will work. If it's a number you can use - where mod(field,100) in (0,1)
Wilder1626Author Commented:
Thanks a lot for all your help.

It was finally not to hard to do what i needed. Substr was perfect.

Thanks again to all of you that help me understand multiple options.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.