This forum is currently locked. You can't register or post questions at this time. (read more)

Like Operator

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

gnkdev
Member
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Like Operator

Postby gnkdev » Sat Feb 04, 2012 6:47 am

Hi,

how do we find the value GZ120804 from the string "978/955086/GZ120804/10-FEB-12"

*Not to use like operator instead pointing the exact location of the string
*The length of the original value may differ.

Thanks & Regards,
Navin Kumar G

tonis
Senior Member
Posts: 150
Joined: Wed Sep 30, 2009 6:13 pm
Location: Thessaloniki, Greece

Re: Like Operator

Postby tonis » Sat Feb 04, 2012 8:15 am

Hi,
Regular expressions might do the job for you.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Like Operator

Postby Tim... » Sat Feb 04, 2012 10:40 am

Hi.

Do you mean you want to find the position in the string? This would be INSTR.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

gnkdev
Member
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Re: Like Operator

Postby gnkdev » Sat Feb 04, 2012 11:15 am

tonis, I couldn't find any examples with regexp. Could you please brief it out.

tim, I want to find the value after the second backslash and before third backslash. as i said earlier the total length of the value will be differ.

Thanks & Regards,
Navin Kumar G

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Like Operator

Postby Tim... » Sat Feb 04, 2012 1:49 pm

Hi.

Using INSTR and SUBSTR:

Code: Select all

CREATE TABLE t1 (
  col1 VARCHAR2(100)
);

INSERT INTO t1 VALUES ('978/955086/GZ120804/10-FEB-12');
INSERT INTO t1 VALUES ('97/95508/BANANA/10-FEB-12');
COMMIT;

SELECT SUBSTR(col1, INSTR(col1,'/',1,2)+1, INSTR(col1,'/',1,3)-INSTR(col1,'/',1,2)-1)
FROM   t1;

SUBSTR(COL1,INSTR(COL1,'/',1,2)+1,INSTR(COL1,'/',1,3)-INSTR(COL1,'/',1,2)-1)
----------------------------------------------------------------------------------------------------
GZ120804
BANANA

2 rows selected.

test@db11g>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Like Operator

Postby Tim... » Sat Feb 04, 2012 2:14 pm

Hi.

This regular expression works also.

Code: Select all

SELECT REGEXP_SUBSTR(col1, '[^/"]*', 1, 5)
FROM   t1;

REGEXP_SUBSTR(COL1,'[^/"]*',1,5)
----------------------------------------------------------------------------------------------------
GZ120804
BANANA

2 rows selected.

test@db11g>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

gnkdev
Member
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Re: Like Operator

Postby gnkdev » Mon Feb 06, 2012 3:57 am

Thank you ... ! :)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Like Operator

Postby Tim... » Mon Feb 06, 2012 10:17 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron