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

Posted on 2014-12-10
Last Modified: 2014-12-18

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

Question by:Wilder1626
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
LVL 70

Accepted Solution

Qlemo earned 400 total points
ID: 40492854
... Where Substr(SOURCE_ID, -2) IN ('21','24','03','60','67','34','57')
LVL 70

Expert Comment

ID: 40492855
But note that renders any index unusuable, so it might introduce performance penalities.
LVL 49

Assisted Solution

PortletPaul earned 100 total points
ID: 40492868
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Author Comment

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

let me give it a try.

LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493461
Another option could be to create a virtual column with the SUBSTR... and then create an index upon that new virtual column ;-)
LVL 35

Expert Comment

ID: 40493597
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.
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493610
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...
LVL 35

Expert Comment

ID: 40493637
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.
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40493762
another nice link about padding in Oracle:
LVL 32

Expert Comment

ID: 40503057
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)
LVL 11

Author Closing Comment

ID: 40507697
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.

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question