SQL Query in Oracle

[urge262]

Hello,

I am trying to write a query in Oracle.

Here is a sample of the data for the CUST_ID field.
-----CUST_ID---------------------------------
CITFRAVIR01
DHL01
ARINC57
PFIZER11
CARAIRSAN01
---------------------------------------------

I need to write a query that strips off the last 2 digits. So instead of "CARAIRSAN01" I want to return "CARAIRSAN".



The CUST_ID field varies in length and varies in the last 2 digits.

I was trying to play around with the SUBSTR, INSTR, and LENGTH functions together but i couldn't figure it out. So if anyone knows of a different fucntion or a way to use the above functions I would really appreciate it.

 

[lespaul]

This doesn't work for you?:

select substr(Cust_ID, 1, length(Cust_ID) - 2) FROM tblName

Leslie

 

[urge262]

Leslie,

Thank you! I was close! So the "1" means start at the first position right?

Then find the length subtract 2 characters right to left because of the "-" negative 2 and return the string.

thanks again!

 

[lespaul]

yes, the 1 means start at the first position and then you end at the length of the string - 2 so you remove the last two characters!!

Glad you got it!

leslie

 

[urge262]

Thanks again!