SQL for table with column (varchar2 2000) and line break in it
Hi,
I need a sql statement for a table, which simply stores a text. It has a column ID, key1, key2, ..., text, date etc. The text can be entered using a line break (return) in an oracle form.
Code:
ID key1 key2 text date
1 K1 K2 any text a user entered with or without line break in it 01/01/2010
...
Assuming there are line breaks in the text column, the statement should show the result as follows:
Code:
1 K1 K2 any text a user entered 01/01/2010
2 K1 K2 with or without line break 01/01/2010
3 K1 K2 in it 01/01/2010
...
...Assuming there are line breaks in the text column, the statement should show the result as follows:
Code:
1 K1 K2 any text a user entered 01/01/2010
2 K1 K2 with or without line break 01/01/2010
3 K1 K2 in it 01/01/2010
...
Any ideas? ...
Something like this perhaps ?
Code:
SQL>
SQL>
SQL> -- check what the data in the table looks like
SQL> select * from t;
ID KEY1 KEY2 TXT DT
---------- ---- ---- ---------------------------------------- ---------
10 K1 K2 any text a user entered 01-JAN-10
with or without line break
in it
20 J1 J2 just one line here 02-JAN-10
2 rows selected.
SQL>
SQL> -- now run the SELECT statement so as to spread out the multi-line TXT column into as many rows
SQL>
SQL> --
SQL> select id,
2 key1,
3 key2,
4 substr(txt,
5 case x.iter when 1 then 1 else instr(txt,chr(10),1,x.iter-1)+1 end,
6 case
7 when instr(txt,chr(10),1,x.iter) > 0
8 then instr(txt,chr(10),1,x.iter) - case x.iter when 1 then 1 else instr(txt,chr(10),1,x.iter-1) end
9 else length(txt)
10 end
11 ) txt,
12 x.iter num,
13 dt
14 from t,
15 (select level iter
16 from dual
17 connect by level <= (select max(length(txt)-length(replace(txt,chr(10))))+1 from t)
18 ) x
19 where (x.iter = 1 or instr(txt,chr(10),1,1) > 0)
20 order by id, x.iter;
ID KEY1 KEY2 TXT NUM DT
---------- ---- ---- ---------------------------------------- ---------- ---------
10 K1 K2 any text a user entered 1 01-JAN-10
10 K1 K2 with or without line break 2 01-JAN-10
10 K1 K2 in it 3 01-JAN-10
20 J1 J2 just one line here 1 02-JAN-10
4 rows selected.
SQL>
SQL>
Very nice SQL! Looks good, except for the fakt, that it splits an entry in line 1 and line 2 and there is also a line 3 and 4 with the full line? Like
Code:
1 10 K1 K2 any text a user entered 01-JAN-10
2 10 K1 K2 with or without line break 01-JAN-10
3 10 K1 K2 in it 01-JAN-10
4 10 K1 K2 any text a user enteredwith or without line breakin it 01-JAN-10
5 10 K1 K2 any text a user enteredwith or without line breakin it 01-JAN-10
...Looks good, except for the fakt, that it splits an entry in line 1 and line 2 and there is also a line 3 and 4 with the full line? ...
Nope, that's not how it works !
Have a look at the entire post carefully. I have pasted the data in your table before the query is run, and the kind of data the query returns.
So, if you have this in your table -
Code:
ID KEY1 KEY2 TXT DT
---------- ---- ---- -------------------------- ---------
10 K1 K2 any text a user entered 01-JAN-10
with or without line break
in it
20 J1 J2 just one line here 02-JAN-10
2 rows selected.
then the query will work on that data and return this -
Code:
ID KEY1 KEY2 TXT NUM DT
---------- ---- ---- --------------------------- ---- ---------
10 K1 K2 any text a user entered 1 01-JAN-10
10 K1 K2 with or without line break 2 01-JAN-10
10 K1 K2 in it 3 01-JAN-10
20 J1 J2 just one line here 1 02-JAN-10
4 rows selected.
Note:
(1) Table data has 2 rows only. The query output has 4 rows.
(2) TXT value for ID=10 spanned 3 lines in the table. TXT value is split up and displayed as 3 different rows in query output.
(3) Values of ID, KEY1, KEY2 were not repeated in table data. Because ID=10 is for only one row. But ID=10 and KEY1, KEY2 values are repeated in the query output. ID = 10 is for 3 rows in query output, unlike table data where it is true for only one row.
(4) TXT value for ID=20 spanned only one row in the table. TXT value for ID=20 spans only one row in the query output. In general, if TXT value for a particular ID spans only one row i.e. if it does not have any embedded newline character, then it will be displayed as it is.
Ok, lets check in detail what I have ... my table text can be filled from different areas in an application ... there is a header dable and a detail table, and in the app a user is able to enter notes (texttable) on both levels - header and detail. Header ID is 6832698 and if the note is entered on header level, key2 is DFLT. The line_no is the line number on text. If the note has been entered on detail level, key2 is the detail ID, eg. detail record 1 would be key2 = 1, etc.
Table
Code:
REF TYPE KEY1 KEY2 LINE_NO TEXT_LINE DT
ORD OP 6832698 DFLT 1 MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 07.05.2010 11:48:54
ORD OP 6832698 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE 07.05.2010 11:48:54
ORD OP 6832698 1 1 DETAIL LINE 1 TESTREMARK 1 07.05.2010 11:49:41
ORD OP 6832698 2 1 DETAIL LINE 2 TESTREMARK 1 07.05.2010 11:52:56
ORD OP 6832698 1 2 DETAIL LINE 1 TESTREMARK 3 07.05.2010 11:59:50
My select
Code:
select ref,type,key1,key2,line_no,substr(text_line,case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,casewhen instr(text_line,chr(10),1,x.iter)>0then instr(text_line,chr(10),1,x.iter)-case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1) endelse length(text_line)end) text_line,x.iter num,cre_datfrom text t,(selectlevel iterfrom dualconnect by level<=(select max(length(text_line)-length(replace(text_line,chr(10))))+ 1 from text table)) xwhere(x.iter =1 or instr(text_line,chr(10),1,1)>0)order by key1, x.iter;
returns
Code:
REF TYPE KEY1 KEY2 LINE_NO TEXT_LINE NUM DT
ORD OP 6832698 DFLT 1 MY TESTREMARK ON HEADER LINE 1 1 07.05.2010 11:48:54
ORD OP 6832698 DFLT 2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE 1 07.05.2010 11:48:54
ORD OP 6832698 1 1 DETAIL LINE 1 TESTREMARK 1 1 07.05.2010 11:49:41
ORD OP 6832698 2 1 DETAIL LINE 2 TESTREMARK 1 1 07.05.2010 11:52:56
ORD OP 6832698 1 2 DETAIL LINE 1 TESTREMARK 3 1 07.05.2010 11:59:50
ORD OP 6832698 DFLT 1 ANOTHER TESTREMARK ON HEADER LINE 2 2 07.05.2010 11:48:54
ORD OP 6832698 DFLT 1 MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 3 07.05.2010 11:48:54
ORD OP 6832698 DFLT 1 MY TESTREMARK ON HEADER LINE 1ANOTHER TESTREMARK ON HEADER LINE 2 4 07.05.2010 11:48:54
Tyler, awesome query...but I think it had a few flaws as the OP mentioned.
1) It does indeed select more rows than necessary for some lines. Specifically, for every row having more than 1 and fewer line breaks than the maximum number of line breaks found, extraneous lines will be printed. By modifying the WHERE clause this is easily fixed.
2) It includes the line breaks in some cases and others not. Specifically, every row except the first and last would include the line break. I saw this pretty quickly because I made a change to use '+' as the separating character instead of the line break. And, running your query verbatim, I saw a lot of '+' in the output that shouldn't be there.
So, here what I have:
Table:
Code:
SQL> desc tst;
Name Null? Type
----------------------------------------- -------- ----------------------------
KEY NUMBER(10)
TXT VARCHAR2(200)
Data in table:
Code:
SQL> SELECT key, SUBSTR(txt,1,40) txt FROM tst;
KEY TXT
---------- ----------------------------------------
1 This is one line
2 [2]1of2+[2]2of2
3 [3]1of4+[3]2of4+[3]3of4+[3]4of4
4 [4]1of3+[4]2of3+[4]3of3
5 Another one liner
Here is your query; modified slightly for format and so it'd work with my table (change line break to '+'):
Code:
SELECT
key,
SUBSTR(txt,
CASE x.iter
WHEN 1 THEN 1
ELSE INSTR(txt,'+',1,x.iter-1)+1
END,
CASE
WHEN INSTR(txt,'+',1,x.iter) > 0 THEN
INSTR(txt,'+',1,x.iter) -
CASE x.iter
WHEN 1 THEN 1
ELSE INSTR(txt,'+',1,x.iter-1)
END
ELSE LENGTH(txt)
END
) txt,
x.iter num
FROM tst,
(SELECT LEVEL iter FROM dual
CONNECT BY LEVEL <= (SELECT MAX(LENGTH(txt)-LENGTH(REPLACE(txt,'+')))+1 FROM tst)
) x
WHERE (x.iter = 1 OR INSTR(txt,'+',1,1) > 0)
ORDER BY key, x.iter;
And its output from my data:
Code:
KEY TXT NUM
---------- ---------------------------------------- ----------
1 This is one line 1
2 [2]1of2 1
2 [2]2of2 2
2 [2]1of2+[2]2of2 3
2 [2]1of2+[2]2of2 4
3 [3]1of4 1
3 [3]2of4+ 2
3 [3]3of4+ 3
3 [3]4of4 4
4 [4]1of3 1
4 [4]2of3+ 2
4 [4]3of3 3
4 [4]1of3+[4]2of3+[4]3of3 4
5 Another one liner 1
14 rows selected.
You can see the flaws as pointed out about. Here is your query, with changes in bold+red to fix it:
Code:
SELECT
key,
SUBSTR(txt,
CASE x.iter
WHEN 1 THEN 1
ELSE INSTR(txt,'+',1,x.iter-1)+1
END,
CASE
WHEN INSTR(txt,'+',1,x.iter) > 0 THEN
INSTR(txt,'+',1,x.iter) -
CASE x.iter
WHEN 1 THEN 1
ELSE INSTR(txt,'+',1,x.iter-1) + 1
END
ELSE LENGTH(txt)
END
) txt,
x.iter num
FROM tst,
(SELECT LEVEL iter FROM dual
CONNECT BY LEVEL <= (SELECT MAX(LENGTH(txt)-LENGTH(REPLACE(txt,'+')))+1 FROM tst)
) x
WHERE (x.iter <= LENGTH(txt)-LENGTH(REPLACE(txt,'+'))+1)
ORDER BY key, x.iter;
And its output, which is what I believe the OP wants:
Code:
KEY TXT NUM
---------- ---------------------------------------- ----------
1 This is one line 1
2 [2]1of2 1
2 [2]2of2 2
3 [3]1of4 1
3 [3]2of4 2
3 [3]3of4 3
3 [3]4of4 4
4 [4]1of3 1
4 [4]2of3 2
4 [4]3of3 3
5 Another one liner 1
11 rows selected.
Simple fixes, but I figured I'd play around and see if I could get it working. Awesome job, by the way...I wouldn't have thought of that.
Split column data if the table has n number of column's with some record then how to split n number of colmn's line by line with records
Table
---------
Col1 col2 col3 col4 ....................col20
1 2 3 4 .................... 20
a b c d .................... v
... (11 Replies)
please write a shell script
Table
--------------------------
1 2 3 a b c
3 4 5 c d e
7 8 9 f g h
Output should be like this
---------------
1 2 3
3 4 5
7 8 9
a b c
c d e
f g h (1 Reply)
Hi,
I have urls in my input file like this
http://unix.com/abc/def
http://unix.com/kil/min
I want to use the / as separator and print the last content as another column like this
http://unix.com/abc/def def
http://unix.com/kil/min min
I was using awk -F option and then joining the... (3 Replies)
...when the lines use both a colon and commas to separate the parts you want read as information.
The first version of this script used cut and other non-Bash-builtins, frequently, which made it nice and zippy with little more than average processor load in GNOME Terminal but, predictably, slow... (2 Replies)
i have a one column txt file, which has a large amount of data which look like this
a
11
3
b
45
77
r
7
9
blah
blah
blah
what i am trying to do here is for every 3 lines, i want to move the 2nd and 3rd line to the first line, so it will look like this
a 11 3
b 45 77
r 7 9 (8 Replies)
Hi...
i have currently installed db2 version 9.1.2 on my AIX machine...
No matter what i do,.... VARCHAR2 just doesn't seem to be working!
This is the error i ketp getting after i ran a simple create statement...
$db2 "create table tst (name VARCHAR2(30));"
DB21034E The command was... (2 Replies)
:rolleyes: hi there everybody,
i need help,... thanks anyway!
i am working on a very huge table with the name table1. the problem is that i know only one field name in this table...,
working with a ksh environment i don't know how to view the table to check out the field names :confused:.
... (4 Replies)
Hi folks
I have mysql dump which having insert queries, i want to break that file when 10 complete "INSERTS" lines so extract that line and store in 1.sql and 2.sql and for next 10 insert lines.
pls guide me how can i do that.
Regards,
Bash (2 Replies)
Hello
As per requirements, can i Install a MSSQL SERVER on UNIX ,
is it possible., can i install or not.., could u plz give me reply with
some installation details..........
Regards&Thanking You
sreedhar G (1 Reply)