Home » SQL & PL/SQL » SQL & PL/SQL » how to extract line/sting from long (oracle 9i)
how to extract line/sting from long [message #379679] Wed, 07 January 2009 11:00 Go to next message
pp56825
Messages: 4
Registered: January 2009
Location: Warsaw
Junior Member
I have a table with column "message" type long.
i want to extract field begin with :21: and display it in sql select

example of message:
{1:12360418LHJK1G325H14}
{2:XXXXXXXXXXXXX1XXXXX}
{4:
:20:SFIH235139H145
:21:G2345YOG12U5
:79:XXXXXXXXXXXXX 211323 324123 XXXXXXX
OUR REF 15143145XXX346134

I want to extraxt only this info
:21:G2345YOG12U5

in select or view
Re: how to extract line/sting from long [message #379686 is a reply to message #379679] Wed, 07 January 2009 11:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Assuming your newline is chr(10), search for '%'||chr(10)||':21:%' and then isolate the piece between the newlines using substr().

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:426318778291
Re: how to extract line/sting from long [message #379727 is a reply to message #379686] Wed, 07 January 2009 15:42 Go to previous messageGo to next message
pp56825
Messages: 4
Registered: January 2009
Location: Warsaw
Junior Member
thanks
also i find this
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55212348054
i will try it.
one more question.
what is end of line char?

[Updated on: Wed, 07 January 2009 15:43]

Report message to a moderator

Re: how to extract line/sting from long [message #379735 is a reply to message #379679] Wed, 07 January 2009 16:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

>what is end of line char?
It is Operating System (OS) dependent & you chose to not share your OS with us.

Re: how to extract line/sting from long [message #379741 is a reply to message #379735] Wed, 07 January 2009 17:25 Go to previous messageGo to next message
pp56825
Messages: 4
Registered: January 2009
Location: Warsaw
Junior Member
oracle9i@hp-ux

[Updated on: Wed, 07 January 2009 17:25]

Report message to a moderator

Re: how to extract line/sting from long [message #380101 is a reply to message #379679] Fri, 09 January 2009 03:06 Go to previous messageGo to next message
pp56825
Messages: 4
Registered: January 2009
Location: Warsaw
Junior Member
how can i use this function in select
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:426318778291

longsubstr( p_query in varchar2,p_bind in varchar2,p_from_byte in number,p_for_bytes in number )

when i use it like this
select A, B,longsubstr(M.rowid,1,100) text
FROM TABLE M;
it give me error incorrect number of arguments for longsubstr
Re: how to extract line/sting from long [message #380109 is a reply to message #380101] Fri, 09 January 2009 03:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The function definition you showed has 4 parameters, you give it 3 parameters, so what is your problem with the error message?
Use the correct function (t_substr in the link).

Note that anyway you are limited to LONG value less than 32K.

Regards
Michel
Previous Topic: how to identify spaces
Next Topic: Stripping a field in the format xx.xx.xx. into seperate fields
Goto Forum:
  


Current Time: Sat Dec 03 13:37:55 CST 2016

Total time taken to generate the page: 0.12524 seconds