Home » SQL & PL/SQL » SQL & PL/SQL » Extract a Specific Pattern From Random Data (Oracle 10g)
Extract a Specific Pattern From Random Data [message #424016] Tue, 29 September 2009 22:39 Go to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Dear All,

I Have a Table in which there is a column that contains data like below format

P176-322
JA162-11
H331-329
P1-321
K1-6


Now i want to extract a pattern which should start after Any character i.e. A-Z and end Before first occurrence of '-'

e.g From second Row Desired Result is 162 from JA162-11.

I tried below code

 select substr(t.sample_id,instr(t.sample_id,chr(49),1),length(t.sample_id)) from test t


The code will return 162-11 for second row and also chr(49) is just for 1 whereas i have to check all the numbers from 0-9.

Any Help will be appreciated.

Regards,
Kashif
Re: Extract a Specific Pattern From Random Data [message #424018 is a reply to message #424016] Tue, 29 September 2009 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
The best solution is to use Regular Expression.
Be warned that they non-trivial to learn.
Re: Extract a Specific Pattern From Random Data [message #424019 is a reply to message #424016] Tue, 29 September 2009 22:52 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

Use a translate function .

Regards

Balakrishna
Re: Extract a Specific Pattern From Random Data [message #424033 is a reply to message #424016] Wed, 30 September 2009 00:34 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
@BlackSwan Thanks for your hint

Regular_Expression Solved the Problem

Solved Query is as below

select substr(substr(t.sample_id,
regexp_instr(t.sample_id, ':digit:', 1, 1, 0, 'i'),
length(t.sample_id)),1,instr(substr(t.sample_id,
regexp_instr(t.sample_id, ':digit:', 1, 1, 0, 'i'),
length(t.sample_id)),'-',1))
from test t


Quote:
Result of JA162-11 is 162 as desired.


Note: I am not able to use code formatter because it was converting my code into orafaq URL so just use two [ before the : and two ] after the : in the above code.

Regards,
Kashif
Re: Extract a Specific Pattern From Random Data [message #424034 is a reply to message #424016] Wed, 30 September 2009 00:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
According to your requirement,
SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL>select * from t3;

COL1
--------------------
P176-322
JA162-11
H331-329
P1-321
K1-6

5 rows selected.

SQL>select rtrim(regexp_substr(col1,'[0-9]+-'),'-') expr from t3;

EXPR
--------------------
176
162
331
1
1

5 rows selected.


regards,
Delna
Re: Extract a Specific Pattern From Random Data [message #424039 is a reply to message #424016] Wed, 30 September 2009 00:54 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
@Delna Thanks it is really a scale down version of My very very Long Code. Razz

Regards,
Kashif
Re: Extract a Specific Pattern From Random Data [message #424165 is a reply to message #424016] Wed, 30 September 2009 06:31 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Hurray for Oracle Features!

Doubly so for those who can show us how to use them!

Kevin
Previous Topic: Add Check Constraints to table
Next Topic: How can we get row x to row y in a resultset using SQL Standard?
Goto Forum:
  


Current Time: Sun Sep 25 21:30:30 CDT 2016

Total time taken to generate the page: 0.04681 seconds