Login or Register to Ask a Question and Join Our Community


Programming

SQL for table with column (varchar2 2000) and line break in it

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL for table with column (varchar2 2000) and line break in it
# 1  
Old 04-29-2010
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
...

Any ideas? Thanks in advance!
# 2  
Old 04-29-2010
Quote:
Originally Posted by spidermike
...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>

tyler_durden
# 3  
Old 05-02-2010
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

# 4  
Old 05-07-2010
Quote:
Originally Posted by spidermike
...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.

HTH,
tyler_durden
# 5  
Old 05-07-2010
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,
case
when instr(text_line,chr(10),1,x.iter)>0
then 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) end
else length(text_line)
end
) text_line,
x.iter num,
cre_dat
from text t,
(selectlevel iter
from dual
connect by level<=(select max(length(text_line)-length(replace(text_line,chr(10))))+ 1 from text table)
) x
where(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

Hope that explains the difference in output.
# 6  
Old 05-07-2010
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.
# 7  
Old 05-07-2010
That does the trick! But now the iteration for num is not working anymore, and shows just 1 for all lines?!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Break a line content and print as column

Hi, I have urls in my input file like this (1 Reply)
Discussion started by: tmonk1
1 Replies

2. Shell Programming and Scripting

Split column data if the table has n number of column's with some record

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)
Discussion started by: Priti2277
11 Replies

3. Shell Programming and Scripting

Split column data if the table has n number of column's

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)
Discussion started by: Priti2277
1 Replies

4. Shell Programming and Scripting

Break a line content and print as column

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)
Discussion started by: jacobs.smith
3 Replies

5. Shell Programming and Scripting

BASH: Break line, read, break again, read again...

...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)
Discussion started by: SilversleevesX
2 Replies

6. Shell Programming and Scripting

the easiest way to break down this column?

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)
Discussion started by: fedora
8 Replies

7. AIX

problem using VARCHAR2

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)
Discussion started by: VGR
2 Replies

8. UNIX for Dummies Questions & Answers

Extracting column names from a table.. SQL with UNIX

: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)
Discussion started by: fmina
4 Replies

9. Shell Programming and Scripting

how to break mysql dump sql file

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)
Discussion started by: learnbash
2 Replies

10. UNIX for Dummies Questions & Answers

Installing Ms Sql Server 2000 On Unix

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)
Discussion started by: sreedhargunda
1 Replies
Login or Register to Ask a Question