Home » SQL & PL/SQL » SQL & PL/SQL » Data filtering (10g)
Data filtering [message #361091] Tue, 25 November 2008 00:40 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
Gurus,
Data :
Column_1
-----------
12.2.4XD3
12.4.52S10
12.5.2D
12.2.5RS23
12.3.2W1

Now, i just need the numeric values post the last character in the column.

Desired Output
Column_1
-----------
3
10

23
1

like wise..I mean it should identify the next numeric value before returning the set if there is a 0 in the second case then it has to return 10 while it'll return just 1 in the fifth case.

Thanks in advace
Re: Data filtering [message #361094 is a reply to message #361091] Tue, 25 November 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 19 November 2008 14:44
Thanks for posting sample data but we can't with. Posting a test case is posting create table and insert statements along with the result you want with the data you provide.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: Data filtering [message #361130 is a reply to message #361091] Tue, 25 November 2008 02:19 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

use length and substr

yours
dr.s.raghunathan
Re: Data filtering [message #361132 is a reply to message #361091] Tue, 25 November 2008 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regular expressions promise the easiest way to get the desired result: you want to find any number (+) of digits ([:digit:]) anchored to the end ($) of a string. Something like this:

./fa/5343/0/

Although this awkward syntax might terrify you at the first glance, regular expressions are real fun; perhaps you'd want to investigate a little bit further by yourself. If so, this page, as well as this one might be an interesting read.


Now, if anyone knows how to get rid of that silly "wiki" thing down there, I'd be thankful if he/she could give a hint. It is the result of an ordinary copy-paste from SQL*Plus to the Forum, along with the [code] tags.

SQL> select col, regexp_substr(col, '[url=/wiki/:digit:]:digit:[/url]+$') result
  2  from test;

COL                  RESULT
-------------------- --------------------
12.2.4XD3            3
12.4.52S10           10
12.5.2D
12.2.5RS23           23
12.3.2W1             1

SQL>
  • Attachment: regexp.PNG
    (Size: 4.67KB, Downloaded 305 times)
Re: Data filtering [message #361135 is a reply to message #361132] Tue, 25 November 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if anyone knows how to get rid of that silly "wiki" thing down there,

Replace your second [ by its html code: "&#X;" without " and with X=91, before clicking on "Submit".

Regards
Michel
Re: Data filtering [message #361159 is a reply to message #361135] Tue, 25 November 2008 04:21 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think I got it:
SQL> select col, regexp_substr(col, '[[:digit:]]+$') result
  2  from test;

Thank you so very much! ./fa/1578/0/

[Updated on: Tue, 25 November 2008 05:52]

Report message to a moderator

Re: Data filtering [message #361182 is a reply to message #361159] Tue, 25 November 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You got it in preview but "preview" modifies what you wrote and you must redo it before clicking on "submit".

Regards
Michel
Re: Data filtering [message #361195 is a reply to message #361182] Tue, 25 November 2008 05:54 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, true ... it requires higher level of foruming which I, obviously, haven't reached yet.

After modifying my previous attempt and not "previewing" it again, it appears to be OK. Finally.
Re: Data filtering [message #362164 is a reply to message #361091] Mon, 01 December 2008 04:15 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
The regexp will not take of whether alphabet is at the trailing end.
Hence,the code should be needed to take care of the same
Re: Data filtering [message #362203 is a reply to message #362164] Mon, 01 December 2008 05:36 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@J1357,

J1357 wrote on Mon, 01 December 2008 15:45
The regexp will not take of whether alphabet is at the trailing end.
Hence,the code should be needed to take care of the same


Please prove it with a test case. I think with the examples you gave the soluton given by Littlefoot works just fine.

Regards,
Jo
Re: Data filtering [message #362452 is a reply to message #361091] Tue, 02 December 2008 06:16 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
Suppose that the data is
Column_1
-----------
12.2.4XD3e
12.4.52S10d
12.5.2D
12.2.5RS23e-1
12.3.2W1a

the column that forms just the output has leading and trailing 0

Column_2
-----------
3e000000
0001000d
<null>
00023e-1
0001a000

Hence, for this column which is column_2 i need just the
Output to be
------------
3e
10d
<null>
23e-1
1a

Thats why i had posted for last number,But i was wrong as it can be either numeric or alphabet or special character.Help needed.
Re: Data filtering [message #362460 is a reply to message #362452] Tue, 02 December 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Fri, 28 November 2008 12:16
First Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Data filtering [message #362841 is a reply to message #362452] Thu, 04 December 2008 04:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How do you know from looking at 0001000d that you need to extract 10d?
Re: Data filtering [message #362846 is a reply to message #361091] Thu, 04 December 2008 05:05 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
That is stored in a separate column ...
Re: Data filtering [message #362888 is a reply to message #362846] Thu, 04 December 2008 07:10 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In a separate column? What column? Do you see any column? I don't.
Re: Data filtering [message #362890 is a reply to message #362846] Thu, 04 December 2008 07:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You said:
Column_2
-----------
3e000000
0001000d
<null>
00023e-1
0001a000

Hence, for this column which is column_2 i need just the
Output to be
------------
3e
10d
<null>
23e-1
1a

Now, the first,third,fourth and fifth are relatively easy to derive - just strip off leading and trailing 0s
For the second one, we seem to need to strip the leading zeroes and then remove 2 out of 3 internal zeroes as well.

Would you care to provide details of the exact process you'd like us to perform?
Previous Topic: Data output formatted
Next Topic: Error in insert query.
Goto Forum:
  


Current Time: Fri Dec 02 14:20:17 CST 2016

Total time taken to generate the page: 0.16160 seconds