Home » SQL & PL/SQL » SQL & PL/SQL » Extracting part of a string
Extracting part of a string [message #574346] Wed, 09 January 2013 05:49 Go to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi,

I need help to make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));

Flist field content with below record:

FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP

Could you please help me to filter the above record from FLIST column thorugh sql script as below:

FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP

means remove first 5 charator and after APXXXXXXXXX.

Output of SQL query should come like below:

CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION

Please let me know if you need any more information on this.

Thanks a lot in advance...


[EDITED by LF: after fellow moderator split this discussion from "How can i get this using regular function reg_substr", I'm removing the "How can I ..." part from a topic title]

[Updated on: Thu, 10 January 2013 00:03] by Moderator

Report message to a moderator

Re: How can i get this using regular function reg_substr [message #574354 is a reply to message #574346] Wed, 09 January 2013 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
A VALID test case:
SQL> Create table test(flist not null VARCHAR2(200));
Create table test(flist not null VARCHAR2(200))
                                 *
ERROR at line 1:
ORA-00907: missing right parenthesis

Regards
Michel
Re: How can i get this using regular function reg_substr [message #574355 is a reply to message #574354] Wed, 09 January 2013 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select substr(flist, 6, instr(flist, ' AP')-6) from test;
SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)
------------------------------------------------------------------
< CONNE_IMPORT_WRONG_COMP_LENG>
< CALL_FUNCTION_OPEN_ERROR>
< OPEN_DATASET_NO_AUTHORITY>
< RAISE_EXCEPTION>

Regards
Michel

[Updated on: Wed, 09 January 2013 07:23]

Report message to a moderator

Re: How can i get this using regular function reg_substr [message #574359 is a reply to message #574346] Wed, 09 January 2013 07:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
Why are you hijacking someone else's post?

SY.
Re: How can i get this using regular function reg_substr [message #574360 is a reply to message #574355] Wed, 09 January 2013 07:54 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi Michel,

Thanks a lot for quick reply...
actaully flist field content in the test table with below information:

FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0
FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0
FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0
FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro.c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV
FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF011 DefaultOpenCL003922RT01420130108174226SR00516128SP0
FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun.c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0

and my requirement is to get output like below from above field contents:
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY

Could you please help me to get desired output.

Thanks a lot in advance...
Re: How can i get this using regular function reg_substr [message #574361 is a reply to message #574360] Wed, 09 January 2013 08:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
Again, don't hijack someone else's post. Open your own post.

SY.
Need Help to make sql query [message #574363 is a reply to message #574346] Wed, 09 January 2013 08:42 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi,

I need help to make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));


flist field content with below 6 information:

1)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0
2)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0
3)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0
4)FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV
5)FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0
6)FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0

and my requirement is to get output like below :

CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY

Could you please help me to get desired output.

Thanks a lot in advance...
Re: Need Help to make sql query [message #574364 is a reply to message #574363] Wed, 09 January 2013 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 22499
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

how does Oracle differentiate between "special" & other characters?
post SQL & results that contains only "special" characters.
Re: Need Help to make sql query [message #574365 is a reply to message #574364] Wed, 09 January 2013 08:56 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi BlackSwan ,

I am not getting clue from the above link.
i have tried this sql query:
select substr(flist, 6, instr(flist, ' AP')-6) from test;

but not getting desired output:
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY

Could you please help me to get desired output.

Thanks
Re: Need Help to make sql query [message #574367 is a reply to message #574365] Wed, 09 January 2013 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22499
Registered: January 2009
Senior Member
It bad & unprofessional to store multiple values in a single column.
You now pay the price for this flawed design implementation.
How can we reproduce what you report?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: How can i get this using regular function reg_substr [message #574370 is a reply to message #574360] Wed, 09 January 2013 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I split your topic to a new one named "Extracting part of a string".
You have to explain with WORDS what are the rules to extract the string you want.

Regards
Michel


Re: How can i get this using regular function reg_substr [message #574390 is a reply to message #574370] Wed, 09 January 2013 22:40 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member

Hi Michel,

I need help to make SQL query to extract data, after FC0XX and before AP008 string.

Create table test(flist not null VARCHAR2(200));


flist field content with below 6 information:

1)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0
2)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0
3)FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0
4)FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV
5)FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0
6)FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0

and my requirement is to extract output as below :

CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY

Could you please help me to get desired output.

Thanks in a advance..
Re: How can i get this using regular function reg_substr [message #574391 is a reply to message #574390] Wed, 09 January 2013 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 22499
Registered: January 2009
Senior Member
why do you continue to NOT follow Posting Guidelines?
Did you actually read the Posting Guidelines?
What item numbers did you NOT understand; list by actual number?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Wed, 09 January 2013 22:52]

Report message to a moderator

Re: How can i get this using regular function reg_substr [message #574393 is a reply to message #574390] Wed, 09 January 2013 23:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
1)Read the forum guidelines and start following them.

2)Provide proper test case as said so far

3)When you post your question, show us what you tried before posting the question.

Here is the query with desired output

WITH TBL AS (
  SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0' FLIST FROM DUAL
    UNION ALL
  SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0' FROM DUAL
    UNION ALL
  SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0' FROM DUAL
    UNION ALL
  SELECT 'FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV' FROM DUAL
    UNION ALL
  SELECT 'FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0' FROM DUAL
    UNION ALL
  SELECT 'FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0' FROM DUAL)
SELECT SUBSTR(FLIST, 6, INSTR(FLIST, 'AP')-6) FROM TBL  

SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)
---------------------------------------
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY


Now, please don't add any other scenario.

regards,
Delna
Re: How can i get this using regular function reg_substr [message #574408 is a reply to message #574393] Thu, 10 January 2013 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not the solution I gave?

Regards
Michel
Re: How can i get this using regular function reg_substr [message #574411 is a reply to message #574408] Thu, 10 January 2013 02:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I guess you considered following as input

Quote:
FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP


and based on it, you put one space before expression 'AP' in function SUBSTR

regards,
Delna
Re: How can i get this using regular function reg_substr [message #574413 is a reply to message #574411] Thu, 10 January 2013 03:04 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Thanks a million Delna and Michel for help to get desired output. SQL query is working fine.
actually i was confused with forum guideline. Thanks to Michel to split this topic in to new one.
Re: How can i get this using regular function reg_substr [message #574417 is a reply to message #574413] Thu, 10 January 2013 03:48 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi Michel/Delna,

WITH TBL AS (

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0' FLIST FROM DUAL

UNION ALL

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV' FROM DUAL

UNION ALL

SELECT 'FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC022SAPSQL_STMNT_TOO_LARGEAP015/1BCDWB/DBZYC09AI015/1BCDWB/DBZYC09AL003555TD03250EA6F2EBBAE1B10E10080003E5706AAXC017CX_SY_OPEN_SQL_ DBCM040//bas/640_REL/src/krn/runt/absapsql.c#28CF016HandleRsqlErrors'

FROM DUAL)

SELECT SUBSTR(FLIST, 6, INSTR(FLIST, 'AP')-6) FROM TBL;

SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)

------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------

CALL_FUNCTION_OPEN_ERROR

CALL_FUNCTION_OPEN_ERROR

CALL_FUNCTION_OPEN_ERROR

MESSAGE_TYPE_X

TIME_OUT

RFC_NO_AUTHORITY

S


i am getting output as "S" instead of "SAPSQL_STMNT_TOO_LARGE" .

Could you please help me what correction we have to make in sql query.

Thanks a lot....
Re: How can i get this using regular function reg_substr [message #574420 is a reply to message #574417] Thu, 10 January 2013 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 09 January 2013 14:18
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Also always post your Oracle version, with 4 decimals.
...


Michel Cadot wrote on Wed, 09 January 2013 16:42
...
You have to explain with WORDS what are the rules to extract the string you want.
...


Please fulfil the requirements.
It is rude to not follow what we ask and the result is just NO HELP.

Regards
Michel

[Updated on: Thu, 10 January 2013 03:53]

Report message to a moderator

Re: How can i get this using regular function reg_substr [message #574436 is a reply to message #574420] Thu, 10 January 2013 05:36 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi Michel/Delna,

WITH TBL AS (

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E6E4A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174840SR00516128SP0' FLIST FROM DUAL

UNION ALL

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV' FROM DUAL

UNION ALL

SELECT 'FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0' FROM DUAL

UNION ALL

SELECT 'FC022SAPSQL_STMNT_TOO_LARGEAP015/1BCDWB/DBZYC09AI015/1BCDWB/DBZYC09AL003555TD03250EA6F2EBBAE1B10E10080003E5706AAXC017CX_SY_OPEN_SQL_ DBCM040//bas/640_REL/src/krn/runt/absapsql.c#28CF016HandleRsqlErrors'

FROM DUAL)

SELECT SUBSTR(FLIST, 6, INSTR(FLIST, 'AP')-6) FROM TBL;

SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)

------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------

CALL_FUNCTION_OPEN_ERROR

CALL_FUNCTION_OPEN_ERROR

CALL_FUNCTION_OPEN_ERROR

MESSAGE_TYPE_X

TIME_OUT

RFC_NO_AUTHORITY

S


i am getting output as "S" instead of "SAPSQL_STMNT_TOO_LARGE" .

Could you please help me what correction we have to make in sql query.

Thanks a lot....
Re: How can i get this using regular function reg_substr [message #574439 is a reply to message #574436] Thu, 10 January 2013 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 10 January 2013 10:52
...
Michel Cadot wrote on Wed, 09 January 2013 16:42
...
You have to explain with WORDS what are the rules to extract the string you want.
...


Please fulfil the requirements.
It is rude to not follow what we ask and the result is just NO HELP.

Regards
Michel


Re: How can i get this using regular function reg_substr [message #574443 is a reply to message #574439] Thu, 10 January 2013 07:01 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi Michel,

There is one table test is content with flist field.
Create table test
(
flist  VARCHAR2(200)       not null
);

insert into test (flist) 
value('FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250EC550B396D6DC0E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174826SR00516128SP0')
insert into test (flist)
value('FC024CALL_FUNCTION_OPEN_ERRORAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8F463A79F7480E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/ ab rfcfun.c#36CF011DefaultOpenCL003922RT01420130108174525SR00516128SP0')
insert into test (flist)
value('FC014MESSAGE_TYPE_XAP008SAPUP50RAI008UP50RU01AL0041047TD03250EC80F424540890E10080003E5706A5CM040//bas/640_REL/src/krn/runt/abdynpro .c #28CF008ab_jmessCL0041134RT01420130108174225SR00516128SP006172032SV')
insert into test (flist)
value('FC024TIME_OUTAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8E696A97D6F70E10080003E5706A6CM039//bas/640_REL/src/krn/rfc/abrfcfun.c#36CF0 11 DefaultOpenCL003922RT01420130108174226SR00516128SP0')
insert into test (flist)
value('FC024RFC_NO_AUTHORITYAP008SAPLZFI2AI008LZFI2U02AL003613TD03250E8D518CC9B2D10E10080003E5706A8CM039//bas/640_REL/src/krn/rfc/abrfcfun .c #36CF011DefaultOpenCL003922RT01420130108174213SR00516128SP0')
insert into test (flist)
value('FC022SAPSQL_STMNT_TOO_LARGEAP015/1BCDWB/DBZYC09AI015/1BCDWB/DBZYC09AL003555TD03250EA6F2EBBAE1B10E10080003E5706AAXC017CX_SY_OPEN_SQL_ DBCM040//bas/640_REL/src/krn/runt/absapsql.c#28CF016HandleRsqlErrors')

commit;


SELECT SUBSTR(FLIST, 6, INSTR(FLIST, 'AP')-6) FROM test;

SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)
--------------------------------------------------------------------
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY
S


Desired output:-
my requirement is to get output as below:
SUBSTR(FLIST,6,INSTR(FLIST,'AP')-6)
---------------------------------------------------------------------
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
CALL_FUNCTION_OPEN_ERROR
MESSAGE_TYPE_X
TIME_OUT
RFC_NO_AUTHORITY
SAPSQL_STMNT_TOO_LARGE

after execuitng above sql query , it works fine for all values except "SAPSQL_STMNT_TOO_LARGE" which is the last value of flist field.

Could you please help me to get exact desired output

Thanks a lot for understanding and help.
Re: How can i get this using regular function reg_substr [message #574446 is a reply to message #574443] Thu, 10 January 2013 07:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Specify the Rules that will apply while extracting the data from records. You are specifying only desired output, not rules.

Although we can give you the query which will produce perfect result as you require, but I am sure you will come again in future that again it does not work for this specific record.

regards,
Delna
Re: How can i get this using regular function reg_substr [message #574447 is a reply to message #574446] Thu, 10 January 2013 07:46 Go to previous messageGo to next message
spandey
Messages: 10
Registered: January 2013
Junior Member
Hi Delna/Michel,

Thanks a lot for assistance...
now i am getting output as per my expectation.

Re: How can i get this using regular function reg_substr [message #574449 is a reply to message #574447] Thu, 10 January 2013 07:56 Go to previous message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I bet it was not so long you come with some other rows that do not give you the result you expect.
By the way, it is fair you post the final query you found.

Regards
Michel
Previous Topic: How can i use Substr to get required result
Next Topic: Going through record set in oracle and doing calculations
Goto Forum:
  


Current Time: Fri Jul 25 09:03:07 CDT 2014

Total time taken to generate the page: 0.05875 seconds