Home » SQL & PL/SQL » SQL & PL/SQL » last 3 charecters (Oracle 9i, Unix)
last 3 charecters [message #301980] Fri, 22 February 2008 10:39 Go to next message
Cathy2008
Messages: 9
Registered: February 2008
Junior Member
I need to get the last 3 charecters from a charecter field and the data is not always have same length. How can i get it.

Thanks,
Cathy

Re: last 3 charecters [message #301981 is a reply to message #301980] Fri, 22 February 2008 10:50 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Use substr function.

Tempting to give the ans , but sorry.
Re: last 3 charecters [message #301982 is a reply to message #301980] Fri, 22 February 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SUBSTR, have a look at what happens if position is negative.

Regards
Michel
Re: last 3 charecters [message #301984 is a reply to message #301980] Fri, 22 February 2008 10:52 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Michel,

Again Posted the same with a fraction sec difference.
2 time co-incident.
Re: last 3 charecters [message #301987 is a reply to message #301980] Fri, 22 February 2008 10:59 Go to previous messageGo to next message
Cathy2008
Messages: 9
Registered: February 2008
Junior Member
I can use the substr function, but the string in the coulumn is not always have the same lengh.

for e.g., the data in the table column looks like below

ID
------------
PEB2005SEB000
XYPZ247200MEBRS
BSD400NYPXPR5780
.
.
.

appreciate your help.
Re: last 3 charecters [message #301988 is a reply to message #301980] Fri, 22 February 2008 11:00 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
you can use this way

reverse(substr(reverse(colname),1,3))
Re: last 3 charecters [message #301989 is a reply to message #301987] Fri, 22 February 2008 11:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read Michels Link. The answer is in the first paragraph.
Re: last 3 charecters [message #301992 is a reply to message #301988] Fri, 22 February 2008 11:04 Go to previous messageGo to next message
Cathy2008
Messages: 9
Registered: February 2008
Junior Member
Excellent!!!, thank you so much, it surved the purpose. I am not aware of reverse function.
Re: last 3 charecters [message #301994 is a reply to message #301992] Fri, 22 February 2008 11:08 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Do NOT use the reverse function. As has already been said, look at Michel's link. Use a negative number as the 2nd parameter. Job done.
Previous Topic: how to find out that how many times trigger fired today
Next Topic: Retrieving rows for which SUM(qty) equals to a specified value ?
Goto Forum:
  


Current Time: Fri Dec 09 02:25:54 CST 2016

Total time taken to generate the page: 0.15397 seconds