Another point which you should keep in mind would be
to provide exact descriptions and sample data covering as many cases as possible for your requirements.
For example:
1) Your output sample were lower cases while input were upper cases.
2) All element length in your sample were 2.
If those were all possible cases, the following sample would be enough
Example 1: Up to 100 elements.
Note: this might not be your expected solution. But, returned exactly same result you wanted.
Code:
------------------------------ Commands Entered ------------------------------
WITH
t(id , loc) AS (
VALUES
( 1 , 'NJ@NY@PA@CA' )
, ( 2 , 'CT' )
, ( 3 , 'CA@VA' )
)
SELECT VARCHAR(id) || ',' ||
LOWER( SUBSTR(loc , k * 3 - 2 , 2) ) AS output
FROM t
INNER JOIN (
SELECT k1 + k2 AS k
FROM (VALUES 1, 2, 3, 4, 5, 6, 7, 8, 9,10 ) k(k1)
, (VALUES 0,10,20,30,40,50,60,70,80,90 ) k(k2)
) k
ON k <= LENGTH(loc) / 3 + 1
ORDER BY
id , k
;
------------------------------------------------------------------------------
OUTPUT
--------------
1,nj
1,ny
1,pa
1,ca
2,ct
3,ca
3,va
7 record(s) selected.
dBforums
>
Splitting A Record
02-03-12, 14:58
Linear Mode
