Home » SQL & PL/SQL » SQL & PL/SQL » Removing the first occurance of a number in a string
Removing the first occurance of a number in a string [message #224852] Thu, 15 March 2007 13:55 Go to next message
CWISE
Messages: 12
Registered: March 2003
Junior Member
I am trying to find a way to remove the first occurance of a number in a string. An example would be:

BlahBlah 2.5" XXXX

needs to be

BlahBlah

The normal trim or RTrim are good at stripping out the part of the string from a set character place. The strings in this field can vary in length, so it has to be the first time a number shows up in the string.

thanks in advance
Re: Removing the first occurance of a number in a string [message #224855 is a reply to message #224852] Thu, 15 March 2007 14:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What about the
".5 XXXX
You only said to remove first occurrence of a number. A number in that string is 2, yet you show that the output should be BlahBlah. You are not being clear.
Re: Removing the first occurance of a number in a string [message #224862 is a reply to message #224855] Thu, 15 March 2007 15:25 Go to previous messageGo to next message
CWISE
Messages: 12
Registered: March 2003
Junior Member
I have a string column with values like

Chevy 2.5" QQQQ
Automobile 5.5" XXXX

I need to have the results look like this:

Chevy
Automobile

As soon as it hits that 2 (2.5") or 5 (5.5") it trims everything to the right. Would it be a substr or instr?
Re: Removing the first occurance of a number in a string [message #224869 is a reply to message #224862] Thu, 15 March 2007 16:07 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
If you are using 10g then

regexp_replace(col_name, '\d.*$', null)

will remove everything from the first digit to the end of the string. If you want all space characters in front of the first number removed as well, then use:

regexp_replace(col_name, ' *\d.*$', null)

JR

PS. Don't forget to include your oracle version when posting as the best solution may be version dependent.

Re: Removing the first occurance of a number in a string [message #224870 is a reply to message #224869] Thu, 15 March 2007 16:12 Go to previous messageGo to next message
CWISE
Messages: 12
Registered: March 2003
Junior Member
Sorry about that. I am using 10g. Thanks alot. I will try this out shortly.
Re: Removing the first occurance of a number in a string [message #224986 is a reply to message #224870] Fri, 16 March 2007 06:49 Go to previous messageGo to next message
CWISE
Messages: 12
Registered: March 2003
Junior Member
It worked perfect. Thank you very much.
Re: Removing the first occurance of a number in a string [message #225093 is a reply to message #224986] Sat, 17 March 2007 06:18 Go to previous messageGo to next message
CWISE
Messages: 12
Registered: March 2003
Junior Member
We may end up having to use this on a 9i database. If that is the case what string function would you use? The reg_exp_replace did not exist until 10g right? Any help would be greatly appreciated.
Re: Removing the first occurance of a number in a string [message #225103 is a reply to message #225093] Sat, 17 March 2007 10:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select name
  2     , substr(name, 1, instr(translate(name, '1234567890', '**********'),'*')-2) "Just Name"
  3  from test1;

NAME                 Just Name
-------------------- --------------------
Chevy 2.5" QQQQ      Chevy
Automobile 5.5" XXXX Automobile
Re: Removing the first occurance of a number in a string [message #225111 is a reply to message #225103] Sat, 17 March 2007 14:53 Go to previous message
CWISE
Messages: 12
Registered: March 2003
Junior Member
Perfect! Thank you very much.
Previous Topic: Insert result of SELECT * into table without knowing column names (merged)
Next Topic: Taking Input from console and Displaying Message using DBMS_OUTPUT.PUT_LINE
Goto Forum:
  


Current Time: Fri Dec 02 23:08:01 CST 2016

Total time taken to generate the page: 0.13797 seconds