Home » SQL & PL/SQL » SQL & PL/SQL » Complex use of substring fucntion
Complex use of substring fucntion [message #260251] Fri, 17 August 2007 15:41 Go to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
HI there,

This forum has been a great help for new bee like me.

I have a simple question with the use of substr function. I have this data coming up from my query as follows. I am only interested in getting the string before the numeric (like INTERFACE, COLLECTED_DATA, KEY_ID) and shedding off the 1 or two digits in the very end. I do not have that much experince with substr function so your help will be greatly appreciated.

INTERFACE1
INTERFACE10
INTERFACE11
INTERFACE19
INTERFACE2
INTERFACE20
INTERFACE28
INTERFACE29
INTERFACE3
INTERFACE30
INTERFACE31
INTERFACE4
INTERFACE5
INTERFACE6
INTERFACE7
INTERFACE8
INTERFACE9
COLLECTED_DATA1
COLLECTED_DATA10
COLLECTED_DATA28
COLLECTED_DATA29
COLLECTED_DATA3
COLLECTED_DATA30
COLLECTED_DATA6
COLLECTED_DATA8
COLLECTED_DATA9
KEY_ID2
KEY_ID23
KEY_ID27
KEY_ID31
KEY_ID4
KEY_ID7
KEY_ID8
KEY_ID9
Re: Complex use of substring fucntion [message #260253 is a reply to message #260251] Fri, 17 August 2007 15:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Since you didn't provide Oracle version, here is a 10g version:

SQL> select item, regexp_replace(item, '\d+$') from test_sub;

ITEM                 REGEXP_REPLACE(ITEM,
-------------------- --------------------
INTERFACE1           INTERFACE
INTERFACE10          INTERFACE
INTERFACE11          INTERFACE
INTERFACE19          INTERFACE
INTERFACE2           INTERFACE
INTERFACE20          INTERFACE
INTERFACE28          INTERFACE
INTERFACE29          INTERFACE
INTERFACE3           INTERFACE
INTERFACE30          INTERFACE
INTERFACE31          INTERFACE
INTERFACE4           INTERFACE
INTERFACE5           INTERFACE
INTERFACE6           INTERFACE
INTERFACE7           INTERFACE
INTERFACE8           INTERFACE
INTERFACE9           INTERFACE
COLLECTED_DATA1      COLLECTED_DATA
COLLECTED_DATA10     COLLECTED_DATA
COLLECTED_DATA28     COLLECTED_DATA
COLLECTED_DATA29     COLLECTED_DATA
COLLECTED_DATA3      COLLECTED_DATA
COLLECTED_DATA30     COLLECTED_DATA
COLLECTED_DATA6      COLLECTED_DATA
COLLECTED_DATA8      COLLECTED_DATA
COLLECTED_DATA9      COLLECTED_DATA
KEY_ID2              KEY_ID
KEY_ID23             KEY_ID
KEY_ID27             KEY_ID
KEY_ID31             KEY_ID
KEY_ID4              KEY_ID
KEY_ID7              KEY_ID
KEY_ID8              KEY_ID
KEY_ID9              KEY_ID

34 rows selected.
Re: Complex use of substring fucntion [message #260255 is a reply to message #260251] Fri, 17 August 2007 16:12 Go to previous messageGo to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
Sorry that I did not provide this information earlier. I am using Oracle 9i.

Re: Complex use of substring fucntion [message #260258 is a reply to message #260255] Fri, 17 August 2007 16:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If the numbers are always at the end of the data, you should consider TRANSLATE.
Re: Complex use of substring fucntion [message #260259 is a reply to message #260255] Fri, 17 August 2007 16:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How about this?
select rtrim(<column>,'0123456789') from <table>;
By
Vamsi
Re: Complex use of substring fucntion [message #260260 is a reply to message #260258] Fri, 17 August 2007 16:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
First I thought about TRANSLATE only.
But always I had a problem with it, if I want to remove something from the string.
I can't use
select translate('ABC1','0123456789','') from dual; -- will give nothing
(or)
select translate('ABC1','0123456789',' ') from dual;--give leave a space
So, I came up with this Cool
select translate('ABC1','A0123456789','A') from dual;
Is there any other way?

By
Vamsi
Re: Complex use of substring fucntion [message #260262 is a reply to message #260260] Fri, 17 August 2007 16:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Vamsi,
translate('ABC1','A0123456789','A') is a method to use TRANSLATE in this case, however as you mentioned RTRIM would be just as effective.
Re: Complex use of substring fucntion [message #260265 is a reply to message #260251] Fri, 17 August 2007 17:03 Go to previous messageGo to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
I got my things resolved with RTRIM.

Thanks to EBRIAN and VAMSI for your help. Really appreciate it
Re: Complex use of substring fucntion [message #260280 is a reply to message #260251] Sat, 18 August 2007 00:15 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
U can use this

select rtrim(ltrim('12hye21','1234567890'),'1234567890') val
from dual

Output
hye
Re: Complex use of substring fucntion [message #260329 is a reply to message #260280] Sat, 18 August 2007 09:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
muzahidul islam wrote on Sat, 18 August 2007 01:15

select rtrim(ltrim('12hye21','1234567890'),'1234567890') val
from dual

The OP is interested in trimming numbers at the very end of the string, therefore ltrim is unnecessary.
Re: Complex use of substring fucntion [message #260615 is a reply to message #260280] Mon, 20 August 2007 08:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
muzahidul islam wrote on Sat, 18 August 2007 01:15
U can use this

select rtrim(ltrim('12hye21','1234567890'),'1234567890') val
from dual



And please stop using this word: U
Re: Complex use of substring fucntion [message #260618 is a reply to message #260251] Mon, 20 August 2007 08:21 Go to previous messageGo to next message
rookiedba
Messages: 11
Registered: March 2007
Location: Montana
Junior Member
Good Morning freinds,

I really appreciate the comments that you guys had posted. This issue is resolved with the help from ebrian, vamsi, mazharul and
joy_division. Please do not post any more coments on this post.

Thank you all

RD

Re: Complex use of substring fucntion [message #260620 is a reply to message #260618] Mon, 20 August 2007 08:36 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rookiedba wrote on Mon, 20 August 2007 09:21
Good issue is resolved with the help from ebrian, vamsi, mazharul and
joy_division. Please do not post any more coments on this post.



Rookie, your wish has been fulfilled. I'll also admit that I did not offer any help, but thanks for the thanks.
Previous Topic: How to fetch data frm "long" data type column
Next Topic: Loading external library
Goto Forum:
  


Current Time: Sun Dec 04 00:44:54 CST 2016

Total time taken to generate the page: 0.13285 seconds