Home » SQL & PL/SQL » SQL & PL/SQL » Need help in extracting a string from text.......
icon5.gif  Need help in extracting a string from text....... [message #211915] Wed, 03 January 2007 01:40 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
I have following types of text:

EVPOpen1
CCPPeo3a
CCPFin2bca
AO5
MB48abc
etc....

I need to extract the string before the FIRST no.(1 or 3 or 2 or 4) in the text. Which SQL function can i use and how?

I also need to extract string after the LAST no. in the text(say after 8 in MB48abc).

substr() function would help only incase the position of the number in the text is fix, but that is not the case here....

kindly advise.

Regards

[Updated on: Wed, 03 January 2007 01:48]

Report message to a moderator

Re: Need help in extracting a string from text....... [message #211921 is a reply to message #211915] Wed, 03 January 2007 02:06 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Will this work?

SQL> CREATE TABLE test (col1 VARCHAR2(30));

Table created.

SQL> INSERT INTO test VALUES ('EVPOpen1');

1 row created.

SQL> INSERT INTO test VALUES ('CCPPeo3a');

1 row created.

SQL> INSERT INTO test VALUES ('CCPFin2bca');

1 row created.

SQL> INSERT INTO test VALUES ('AO5');

1 row created.

SQL> SELECT REGEXP_SUBSTR(col1, '^[a-zA-Z4-90]*') AS before,
  2         SUBSTR(REGEXP_SUBSTR(col1, '[1-3].*$'), 2) AS after
  3    FROM test;

BEFORE                         AFTER
------------------------------ -----------------------------
EVPOpen
CCPPeo                         a
CCPFin                         bca
AO5
Re: Need help in extracting a string from text....... [message #211928 is a reply to message #211921] Wed, 03 January 2007 02:28 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Just to add that REGEXP_SUBSTR is available only from Oracle 10G.

Frank,
correct me if I am wrong.

[Updated on: Wed, 03 January 2007 02:29]

Report message to a moderator

Re: Need help in extracting a string from text....... [message #211931 is a reply to message #211928] Wed, 03 January 2007 02:38 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Correct. Here is a possible solution for Oracle 9i and below:

SQL> SELECT SUBSTR(col1, 1,
  2           GREATEST( INSTR(col1, '1'), INSTR(col1, '2'), INSTR(col1, '3'))-1),
  3         SUBSTR(col1,
  4           GREATEST( INSTR(col1, '1'), INSTR(col1, '2'), INSTR(col1, '3'))+1)
  5    FROM test;

SUBSTR(COL1,1,GREATEST(INSTR(C SUBSTR(COL1,GREATEST(INSTR(COL
------------------------------ ------------------------------
EVPOpen
CCPPeo                         a
CCPFin                         bca
                               AO5
Re: Need help in extracting a string from text....... [message #211945 is a reply to message #211931] Wed, 03 January 2007 03:13 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi Frank,
Thank you for the query, Sorry i had not frmaed my question appropriatly.

For string like 'MB48abc', expected result is


Before After
MB 48abc



For string like 'ABC1bc', expected result is


Before After
ABC 1bc


How can we modify the query given by you to get this result?
Re: Need help in extracting a string from text....... [message #211949 is a reply to message #211945] Wed, 03 January 2007 03:38 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Please see the result when i fire following query:

SELECT SUBSTR('DP4ABC', 1,
GREATEST( INSTR('DP4ABC', '1'), INSTR('DP4ABC', '2'), INSTR('DP4ABC', '3'))-1),
SUBSTR('DP4ABC',
GREATEST( INSTR('DP4ABC', '1'), INSTR('DP4ABC', '2'), INSTR('DP4ABC', '3'))+1)
FROM dual;


SUBSTR('DP4ABC',1,GREATEST(INS SUBSTR('DP4ABC',GREATEST(INSTR
------------------------------ ------------------------------
DP4ABC




result for query:

SELECT SUBSTR('DP48ABC', 1,
GREATEST( INSTR('DP48ABC', '1'), INSTR('DP48ABC', '2'), INSTR('DP48ABC', '3'))-1),
SUBSTR('DP48ABC',
GREATEST( INSTR('DP48ABC', '1'), INSTR('DP48ABC', '2'), INSTR('DP48ABC', '3'))+1)
FROM dual;


SQL>

SUBSTR('DP48ABC',1,GREATEST(IN SUBSTR('DP48ABC',GREATEST(INST
------------------------------ ------------------------------
DP48ABC
Re: Need help in extracting a string from text....... [message #211952 is a reply to message #211949] Wed, 03 January 2007 03:46 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Yes, try to understand the query and customize it for your requirements:

SQL> SELECT SUBSTR('DP4ABC', 1, INSTR('DP4ABC', '4')-1),
  2         SUBSTR('DP4ABC', INSTR('DP4ABC', '4')+1)
  3  FROM dual
  4  /

SU SUB
-- ---
DP ABC
Re: Need help in extracting a string from text....... [message #211956 is a reply to message #211952] Wed, 03 January 2007 04:09 Go to previous message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Frank, I could tailor it as per my requirement.

Thanks a lot
Previous Topic: SQL datatype
Next Topic: Cursor in a sql statement
Goto Forum:
  


Current Time: Fri Dec 09 13:30:29 CST 2016

Total time taken to generate the page: 0.22932 seconds