Home » SQL & PL/SQL » SQL & PL/SQL » Split the Numeric Value in Varchar (Oracle 9i, Unix)
Split the Numeric Value in Varchar [message #334301] Wed, 16 July 2008 02:31 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hai,

I Need to Split the numeric Values in the Character on remarks.

Ex:

Select Remarks from Pslrcpt;


Remarks
-------------
Challno : 558, Rejctqty 78 Nos.
billno : 61, excess 23 Nos.
Chalno : 5232, shortqty 51 Nos.
Chalno : 7106, qty 78 Nos.

They were Selected the Numeric Values Only.

If it possible please reply me.

Thanks.
Re: Split the Numeric Value in Varchar [message #334302 is a reply to message #334301] Wed, 16 July 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you search before posting?
This has already been asked not so long ago.

Regards
Michel
Re: Split the Numeric Value in Varchar [message #334350 is a reply to message #334301] Wed, 16 July 2008 04:54 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi CVS,

I just give the hint for the query you posted
The Hint is:


SQL> select trim(translate('a01b','ab',' ')) numbers from dual;

NU
--
01


If you search for the above functions you will get the required output.

Thanks
Re: Split the Numeric Value in Varchar [message #334356 is a reply to message #334301] Wed, 16 July 2008 05:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What output do you want?

Given the input of
Challno : 558, Rejctqty 78 Nos.
, which of the followinf is the output you want:
          558           78     
55878
558
558 78
<other>
Re: Split the Numeric Value in Varchar [message #334362 is a reply to message #334356] Wed, 16 July 2008 05:27 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Thanks for reply.

I want both output with space between them or any other character between the numbers.
Re: Split the Numeric Value in Varchar [message #334365 is a reply to message #334362] Wed, 16 July 2008 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - I don't understand what you want.
Given this input:
Challno : 558, Rejctqty 78 Nos.
, show us (wrapped in CODE, /CODE tags to preserve the format) exactly what you want returned.
Re: Split the Numeric Value in Varchar [message #334370 is a reply to message #334365] Wed, 16 July 2008 05:51 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Sir,

I want to Split the quantity in the remarks



Re: Split the Numeric Value in Varchar [message #334374 is a reply to message #334370] Wed, 16 July 2008 05:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
cvs_1984 wrote on Wed, 16 July 2008 11:51
Sir,

I want to Split the quantity in the remarks



You have already said this. But it still is not clear, repeating it does not make it any clearer. Please answer the question(s) asked of you:
JRowbottom wrote on Wed, 16 July 2008 11:31

[/code], show us (wrapped in CODE, /CODE tags to preserve the format) exactly what you want returned.

Re: Split the Numeric Value in Varchar [message #334389 is a reply to message #334370] Wed, 16 July 2008 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to think more clearly about this.

I have provided a list of options, in an easy to understand format that cover all the likely things that I thought you might want, but I didn't provide the one that you do want.

I do not understand what you want.

The only way this situation can change is if you provde us with more information.

Ideally you will provde us with a simple string, showing the characters that you expect to be returned, including spaces, when the input is
Challno : 558, Rejctqty 78 Nos.
Re: Split the Numeric Value in Varchar [message #334420 is a reply to message #334370] Wed, 16 July 2008 07:32 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
cvs_1984 wrote on Wed, 16 July 2008 06:51
Sir,

I want to Split the quantity in the remarks






Maybe you need to ask somebody what the term "show us exactly what you want returned" means because you surely do not comprehend. Please do not try to answer again until you know what that means.
Re: Split the Numeric Value in Varchar [message #334488 is a reply to message #334420] Wed, 16 July 2008 18:28 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

joy_division wrote on Wed, 16 July 2008 12:32
Maybe you need to ask somebody what the term "show us exactly what you want returned" means because you surely do not comprehend. Please do not try to answer again until you know what that means.


Cvs: If I'm going to ask you what exact output do you want if the given is 1 + 1 you are going to answer "I want the total of that two numbers" instead of answering "2".
People here are asking you questions because thay want to help you, but you don't know how to provide good answer.

[Updated on: Wed, 16 July 2008 18:32]

Report message to a moderator

Re: Split the Numeric Value in Varchar [message #334499 is a reply to message #334420] Wed, 16 July 2008 20:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sorry, I just thought this is appropriate at this time:

./fa/4644/0/
  • Attachment: Dilbert.JPG
    (Size: 87.21KB, Downloaded 699 times)
Re: Split the Numeric Value in Varchar [message #334500 is a reply to message #334301] Wed, 16 July 2008 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
After the software is designed & implemented; please tell me how to make run faster.
Re: Split the Numeric Value in Varchar [message #334520 is a reply to message #334499] Wed, 16 July 2008 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing

Good to have a laugh on waking.

Regards
Michel
Re: Split the Numeric Value in Varchar [message #334679 is a reply to message #334301] Thu, 17 July 2008 11:41 Go to previous messageGo to next message
rstapley
Messages: 1
Registered: June 2008
Location: UK
Junior Member
Hi,

I think this may be what you are after:

with t as
(
select   'Challno : 558, Rejctqty 78 Nos.' text_string from dual union all
select   'billno : 61, excess 23 Nos.' from dual union all
select   'Chalno : 5232, shortqty 51 Nos.' from dual union all
select   'Chalno : 7106, qty 78 Nos.' from dual
)
select  TRIM(REPLACE(TRANSLATE(UPPER(text_string)
                     , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ.:;,'
                     , '                             ,')
             , ' ', '')) new_text_string
from    t;


you will notice that I replaced "," with "," so you will get number","number. If you want something else (eg _) then just change the "," after the blanks above.

NEW_TEXT_STRING
558,78
61,23
5232,51
7106,78
Re: Split the Numeric Value in Varchar [message #334770 is a reply to message #334679] Thu, 17 July 2008 23:07 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member


Thanks for yor reply, it has useful to me.
Re: Split the Numeric Value in Varchar [message #334814 is a reply to message #334770] Fri, 18 July 2008 03:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@cvs - all you had to do to get a solution on the same day that you posted the problem was to tell us the output you wanted. Why was this so difficult for you?

If you're on 10g or higher, this is a more compact way of getting the same solution, and it will deal with all additional characters that might crop up in the strings.
with t as (select 'Challno : 558, Rejctqty 78 Nos.' text_string from dual union all
           select 'billno : 61, excess 23 Nos.' from dual union all
           select 'Chalno : 5232, shortqty 51 Nos.' from dual union all
           select 'Chalno : 7106, qty 78 Nos.' from dual)
select regexp_replace(t.text_string,'[^[:digit:],]','');
Previous Topic: website for PL/SQL
Next Topic: Warning: PROCEDURE created with compilation errors. missing Select Key Word
Goto Forum:
  


Current Time: Wed Dec 07 10:55:05 CST 2016

Total time taken to generate the page: 0.06944 seconds