Home » SQL & PL/SQL » SQL & PL/SQL » How to write a single query that delimits the string by count of 3?
How to write a single query that delimits the string by count of 3? [message #258595] Mon, 13 August 2007 02:45 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

How to write a single query that delimits the string by count of 3?

Ex : 'catdograt'

In a single query i should take token as such it returns cat, dog, rat as three seperate records.

Please help out me...

Thanks in advance. Razz

[Updated on: Mon, 13 August 2007 02:45]

Report message to a moderator

Re: How to write a single query that delimits the string by count of 3? [message #258596 is a reply to message #258595] Mon, 13 August 2007 02:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi, look at the substr function. Based on our question, it is exactly what you need.
Re: How to write a single query that delimits the string by count of 3? [message #258601 is a reply to message #258595] Mon, 13 August 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you must have a row generator.
Something like:
SQL> select level from dual connect by level <= 3;
     LEVEL
----------
         1
         2
         3

3 rows selected.

Regards
Michel
Re: How to write a single query that delimits the string by count of 3? [message #258650 is a reply to message #258601] Mon, 13 August 2007 04:34 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Actly i will be given with lengthy string that comes multiples of 3.. from that i have to split the token ...

Can we do it in a single query? I tried with small procedure... but my client want to be done with single query.
Razz
Re: How to write a single query that delimits the string by count of 3? [message #258654 is a reply to message #258650] Mon, 13 August 2007 04:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You got 2 tips:
1. SUBSTR.
2. Row generator

1. With SUBSTR you can take a part of a string from a start position P and N characters long. The length is always 3. The start position P is 1 for the first row and increments by 3.

Row 1: SUBSTR(thetext, 1, 3)
Row 2: SUBSTR(thetext, 4, 3)
....

2. A row generator allows you to generate a record set with X records. We need to split a string in chunks of 3 characters. The number of records needed is the length of the string divided by three. In case of an excess of 1 or 2 extra characters we round up (Oracle has the CEIL function for it).
One row generator is a 'CONNECT BY LEVEL' query from dual:
SELECT LEVEL
FROM  dual
CONNECT BY LEVEL <= number_of_records_needed
the LEVEL pseudcolumn starts with 1 and increments by 1 for each record. Note that there are several other row generator techniques in Oracle. This one is performant and easy to use I think.

If we combine these two we get something like this:

WITH yourtable AS
(
  SELECT 'ABCCATDEFDOFGHIRATJKLCOWMNOBATPQR' thetext FROM dual
)
SELECT SUBSTR( thetext
             , 1+(LEVEL-1)*3-- start position
             , 3 -- string length
             ) str
FROM   yourtable
CONNECT BY LEVEL <= CEIL(LENGTH(thetext)/3)
/


Try that query and see if it is good enough for you.

MHE
Re: How to write a single query that delimits the string by count of 3? [message #258655 is a reply to message #258650] Mon, 13 August 2007 04:52 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Rather than us continually having to supply guesses as to what you want, how about you supply some example data in the form of insert scripts along with a sample of expected results. PLEASE make the data suitably representative. I am not prepared to make guesses as to your requirements
Re: How to write a single query that delimits the string by count of 3? [message #258656 is a reply to message #258655] Mon, 13 August 2007 04:59 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Try with this.
It also works

SELECT SUBSTR('CATDOGRATPATMAT',LEVEL*2+(LEVEL-2),3) AS TEXT
FROM DUAL
CONNECT BY LEVEL <= LENGTH('CATDOGRATPATMAT')/3;





Cheers
Sanka
Re: How to write a single query that delimits the string by count of 3? [message #258659 is a reply to message #258656] Mon, 13 August 2007 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a weak version of Maarten's post.
Quote:
LEVEL*2+(LEVEL-2)

How can you come to that??? Trial and error.
See Maarten's formula, it is a logical one.

Regards
Michel
Re: How to write a single query that delimits the string by count of 3? [message #258662 is a reply to message #258659] Mon, 13 August 2007 05:10 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Yes Michel, it's a weak formula but it's quickly came on my mind so I put it. Thanks a lot for the correction.

Cheers
Sanka
Re: How to write a single query that delimits the string by count of 3? [message #258664 is a reply to message #258662] Mon, 13 August 2007 05:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No, it's not bad. It's a bit "wordy" though (and so is mine):

Yours: LEVEL*2+(LEVEL-2) = LEVEL*2+LEVEL-2 = LEVEL*3-2
Mine: 1+(LEVEL-1)*3 = 1+(LEVEL*3-1*3) = 1+LEVEL*3-3 = LEVEL*3-2

But I wanted to illustrate the logic, that's why I kept my calculation.

MHE
Re: How to write a single query that delimits the string by count of 3? [message #258706 is a reply to message #258664] Mon, 13 August 2007 07:36 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Thanks Maheer for the explanation of both logic.
Now I've understood that logic corrctly.

Cheers
Sanka
icon14.gif  Re: How to write a single query that delimits the string by count of 3? [message #259102 is a reply to message #258706] Tue, 14 August 2007 07:07 Go to previous message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Oh god...

How good it is....

Thank you very much gentleman....

Its working fine.....
Previous Topic: Problem with sub query and MAX
Next Topic: add two dates
Goto Forum:
  


Current Time: Fri Dec 02 18:56:09 CST 2016

Total time taken to generate the page: 0.16022 seconds