Home » SQL & PL/SQL » SQL & PL/SQL » Somebody please explain this query for me.
Somebody please explain this query for me. [message #427440] Thu, 22 October 2009 10:58 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I am trying to create a string tokeniser in pl/sql and found this on of the sites i've been looking.

Select 	LEVEL
, Substr( STRING_TO_TOKENIZE
, Decode( LEVEL, 1, 1, Instr( STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL -1) +1)
, Instr( STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)  
- Decode( LEVEL, 1, 1, Instr( STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL -1) +1) )
			as token
	From ( Select 'H,H,3,J,K,L' || ',' AS STRING_TO_TOKENIZE, ',' AS DELIMITER From Dual ) 
	Connect By Instr( STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) > 0
	Order By LEVEL ASC;


It does work and i tried to run it and the output is

     LEVEL|TOKEN
----------|------------
         1|H
         2|H
         3|3
         4|J
         5|K
         6|L


The problem i have is i dont quite understand how it works or how it is getting those values.

The other thing is i think that this would work if i only want to tokenise numbers. If i want to tokenise a string then ill be in trouble if the string contains commas. Is there a better way of doing it?

Thanks

[Updated on: Thu, 22 October 2009 12:04] by Moderator

Report message to a moderator

Re: Somebody please explain this query for me. [message #427441 is a reply to message #427440] Thu, 22 October 2009 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What part don't you understand?
SUBSTR? INSTR? DECODE? CONNECT BY?

Regards
Michel
Re: Somebody please explain this query for me. [message #427443 is a reply to message #427441] Thu, 22 October 2009 11:16 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I know what all the functions do but i am struggling to put it all together.

I dont quite understand the CONNECT BY clause. I am reading about it now. All the examples i am seeing seem to refer to "CONNECT BY PRIOR" but not as the one used here. I am basically struggling to make sense of how it all comes together.
Re: Somebody please explain this query for me. [message #427445 is a reply to message #427443] Thu, 22 October 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this form it is a row generator, read http://www.orafaq.com/forum/mv/msg/95011/291177/102589/#msg_291177

Regards
Michel
Re: Somebody please explain this query for me. [message #427497 is a reply to message #427440] Thu, 22 October 2009 23:35 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you see the correlation between this part of the query:

Select 'H,H,3,J,K,L' || ',' AS STRING_TO_TOKENIZE, ',' AS DELIMITER From Dual 

and the output of the query:

     LEVEL|TOKEN
----------|------------
         1|H
         2|H
         3|3
         4|J
         5|K
         6|L


Kevin
Previous Topic: Compile view
Next Topic: access previous records
Goto Forum:
  


Current Time: Sat Dec 10 08:41:07 CST 2016

Total time taken to generate the page: 0.08615 seconds