Trim table data to the right 4 chars i.e. right(PIN,4) 3

[mts176]

I am trying to write a query with a few where clauses in it. One of the clauses I want to compare the right 4 chars/nums with a known 4 digit number.

Much like the Right function in ASP

Here is my query

Select * from users Where USERNAME = 'myuser' AND (DOB = to_date('8/6/1964','mm/dd/yyyy') AND right(PIN,4) = 7151) AND EMAIL = 'myuser@user.com'

 

[SantaMufasa]

MTS,

Here is some code that should do what you want:

Select *
  from users
 Where USERNAME = 'myuser'
   AND (DOB = to_date('8/6/1964','mm/dd/yyyy')
        AND substr('0000'||pin,-4) = 7151)
   AND EMAIL = 'myuser@user.com'
/

the '0000' appended to the front of the string causes the code to work properly even if PIN is NULL or less than four positions in length.

Let us know if this resolves your need.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mts176]

Works like a charm. Thank you very much. That was the quickest response I have had. I was a little worried that I wouldn't be able to get this done in a timely fashion.

Thank you once again SantaMufasa.

 

[Beilstwh]

Another easy way of doing it is to use the following

Select * from users
Where USERNAME = 'myuser'
AND (DOB = to_date('8/6/1964','mm/dd/yyyy')
AND PIN like '%7151')
AND EMAIL = 'myuser@user.com';

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

WDITOT, Bill? Have a Purple Star for doing it "the easy way".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ddiamond]

SantaMufasa,

I gave you another star. I had no idea that on oracle you can turn substr into a right function by using a negative starting position. DB2 treats any starting position less than 1 as 1.

- Dan

 

[SantaMufasa]

Very gracious of you, Dan. Thanks. Glad there was something new/interesting/useful for you here. (But I still like Bill's simply clever solution for the original problem.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.