Data filtering [message #361091] |
Tue, 25 November 2008 00:40  |
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 #361132 is a reply to message #361091] |
Tue, 25 November 2008 02:26   |
 |
Littlefoot
Messages: 21823 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:

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 924 times)
|
|
|
|
|
|
|
Re: Data filtering [message #362164 is a reply to message #361091] |
Mon, 01 December 2008 04:15   |
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   |
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   |
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 #362890 is a reply to message #362846] |
Thu, 04 December 2008 07:14  |
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?
|
|
|