Like Operator

[gnkdev]

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]

Hi,
Regular expressions might do the job for you.

[Tim...]

Hi.

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

Cheers

Tim...

[gnkdev]

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...]

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...]

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...

[gnkdev]

Thank you ... !

[Tim...]