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.
Assuming there are line breaks in the text column, the statement should show the result as follows:
Any ideas? Thanks in advance!
...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 -
then the query will work on that data and return this -
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.
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:
Data in table:
Here is your query; modified slightly for format and so it'd work with my table (change line break to '+'):
And its output from my data:
You can see the flaws as pointed out about. Here is your query, with changes in bold+red to fix it:
And its output, which is what I believe the OP wants:
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)