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
# 15  
Old 05-15-2010
Quote:
Originally Posted by spidermike
...
btw now I also see the missunderstanding in the iteration ... your's is for numeric iteration per line break, but I'm looking for an iteration num per line with the same key1 ... means that should show in the case shown above
1
2
3
4
5
6
7
8
You can generate that iteration number by the use of the ROW_NUMBER() analytic function.

Code:
row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,

Here's the modified testcase -

Code:
$ 
$ cat testcase.sql
--
column ref_code format a10
column type format a5
column text_line format a55
column key1 format a6
column key2 format a6

drop table mpr_text;
CREATE TABLE MPR_TEXT
(
  REF_CODE       VARCHAR2(30 CHAR)              NOT NULL,
  TYPE           VARCHAR2(30 CHAR)              NOT NULL,
  KEY1           VARCHAR2(30 CHAR)              NOT NULL,
  KEY2           VARCHAR2(30 CHAR)              NOT NULL,
  LINE_NO        NUMBER(10)                     NOT NULL,
  TEXT_LINE      VARCHAR2(2000 CHAR),
  CRE_USR        VARCHAR2(30 CHAR)              NOT NULL,
  CRE_DAT        DATE                           NOT NULL,
  MOD_USR        VARCHAR2(30 CHAR)              NOT NULL,
  MOD_DAT        DATE                           NOT NULL
);

--
Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', to_date('1/1/2010','mm/dd/yyyy'), 'ME', to_date('2/1/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', to_date('1/15/2010','mm/dd/yyyy'), 'ME', to_date('2/15/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
ANOTHER LINE 1 TESTREMARK 2', 'ME', to_date('2/1/2010','mm/dd/yyyy'), 'ME', to_date('3/1/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
ANOTHER LINE 2 TESTREMARK 2', 'ME', to_date('2/15/2010','mm/dd/yyyy'), 'ME', to_date('3/15/2010','mm/dd/yyyy'));

Insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
Values
('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', to_date('3/1/2010','mm/dd/yyyy'), 'ME', to_date('4/1/2010','mm/dd/yyyy'));

commit;

-- fetch data
select * from mpr_text;

-- run query
select ref_code, 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) + 1 end
                else length(text_line)
              end
             ) text_line,
       x.iter num_by_linebreak,
       row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
       cre_dat
  from mpr_text t,
       ( select level iter
           from dual
        connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
       ) x
 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 order by key1, cre_dat, x.iter;

-- now add a few more rows with a different value of KEY1 to see if this query actually works
-- for more than one KEY1 value
insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '1', 1, 'remark line no 1 of key1=789012, key2=1, line_no=1
remark line no 2 of key1=789012, key2=1, line_no=1', 'ME', to_date('1/20/2010','mm/dd/yyyy'), 'ME', to_date('1/31/2010','mm/dd/yyyy'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '1', 2, 'remark line no 1 of key1=789012, key2=1, line_no=2
remark line no 2 of key1=789012, key2=1, line_no=2
remark line no 3 of key1=789012, key2=1, line_no=2
remark line no 4 of key1=789012, key2=1, line_no=2', 'ME', to_date('3/20/2010','mm/dd/yyyy'), 'ME', to_date('3/31/2010','mm/dd/yyyy'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '2', 1, 'remark line no 1 of key1=789012, key2=2, line_no=1
remark line no 2 of key1=789012, key2=2, line_no=1
remark line no 3 of key1=789012, key2=2, line_no=1', 'ME', to_date('4/20/2010','mm/dd/yyyy'), 'ME', to_date('4/30/2010','mm/dd/yyyy'));

insert into MPR_TEXT
(REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
values
('O', 'OP', '789012', '2', 2, 'remark line no 1 of key1=789012, key2=2, line_no=2', 'ME', to_date('4/25/2010','mm/dd/yyyy'), 'ME', to_date('5/1/2010','mm/dd/yyyy'));

commit;

-- check the data in the table
select * from mpr_text order by key1, cre_dat;

-- now run the query again
select ref_code, 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) + 1 end
                else length(text_line)
              end
             ) text_line,
       x.iter num_by_linebreak,
       row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
       cre_dat
  from mpr_text t,
       ( select level iter
           from dual
        connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
       ) x
 where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 order by key1, cre_dat, x.iter;

$ 
$ 
$

And here's the test run -

Code:
SQL> 
SQL> @testcase
SQL> --
SQL> column ref_code format a10
SQL> column type format a5
SQL> column text_line format a55
SQL> column key1 format a6
SQL> column key2 format a6
SQL> 
SQL> drop table mpr_text;

Table dropped.

SQL> CREATE TABLE MPR_TEXT
  2  (
  3    REF_CODE	 VARCHAR2(30 CHAR) 	     NOT NULL,
  4    TYPE	      VARCHAR2(30 CHAR) 	     NOT NULL,
  5    KEY1	      VARCHAR2(30 CHAR) 	     NOT NULL,
  6    KEY2	      VARCHAR2(30 CHAR) 	     NOT NULL,
  7    LINE_NO	      NUMBER(10)		     NOT NULL,
  8    TEXT_LINE	 VARCHAR2(2000 CHAR),
  9    CRE_USR	      VARCHAR2(30 CHAR) 	     NOT NULL,
 10    CRE_DAT	      DATE			     NOT NULL,
 11    MOD_USR	      VARCHAR2(30 CHAR) 	     NOT NULL,
 12    MOD_DAT	      DATE			     NOT NULL
 13  );

Table created.

SQL> 
SQL> --
SQL> Insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  Values
  4  ('O', 'OP', '123456', 'DFLT', 1, 'MY TESTREMARK ON HEADER LINE 1
  5  ANOTHER TESTREMARK ON HEADER LINE 2', 'ME', to_date('1/1/2010','mm/dd/yyyy'), 'ME', to_date('2/1/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> Insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  Values
  4  ('O', 'OP', '123456', 'DFLT', 2, 'AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE', 'ME', to_date('1/15/2010','mm/dd/yyyy'), 'ME', to_date('2/15/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> Insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  Values
  4  ('O', 'OP', '123456', '1', 1, 'LINE 1 TESTREMARK 1
  5  ANOTHER LINE 1 TESTREMARK 2', 'ME', to_date('2/1/2010','mm/dd/yyyy'), 'ME', to_date('3/1/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> Insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  Values
  4  ('O', 'OP', '123456', '2', 1, 'LINE 2 TESTREMARK 1
  5  ANOTHER LINE 2 TESTREMARK 2', 'ME', to_date('2/15/2010','mm/dd/yyyy'), 'ME', to_date('3/15/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> Insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  Values
  4  ('O', 'OP', '123456', '1', 2, 'LINE 1 TESTREMARK 3', 'ME', to_date('3/1/2010','mm/dd/yyyy'), 'ME', to_date('4/1/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- fetch data
SQL> select * from mpr_text;

REF_CODE   TYPE  KEY1	KEY2	  LINE_NO TEXT_LINE						  CRE_US CRE_DAT   MOD_US MOD_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ------ --------- ------ ---------
O	   OP	 123456 DFLT		1 MY TESTREMARK ON HEADER LINE 1			  ME	 01-JAN-10 ME	  01-FEB-10
					  ANOTHER TESTREMARK ON HEADER LINE 2
O	   OP	 123456 DFLT		2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE	  ME	 15-JAN-10 ME	  15-FEB-10
O	   OP	 123456 1		1 LINE 1 TESTREMARK 1					  ME	 01-FEB-10 ME	  01-MAR-10
					  ANOTHER LINE 1 TESTREMARK 2
O	   OP	 123456 2		1 LINE 2 TESTREMARK 1					  ME	 15-FEB-10 ME	  15-MAR-10
					  ANOTHER LINE 2 TESTREMARK 2
O	   OP	 123456 1		2 LINE 1 TESTREMARK 3					  ME	 01-MAR-10 ME	  01-APR-10

5 rows selected.

SQL> 
SQL> -- run query
SQL> select ref_code, type, key1, key2, line_no,
  2  	    substr(text_line,
  3  		   case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
  4  		   case
  5  		     when instr(text_line,chr(10),1,x.iter) > 0
  6  		     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) + 1 end
  7  		     else length(text_line)
  8  		   end
  9  		  ) text_line,
 10  	    x.iter num_by_linebreak,
 11  	    row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
 12  	    cre_dat
 13    from mpr_text t,
 14  	    ( select level iter
 15  		from dual
 16  	     connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
 17  	    ) x
 18   where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 19   order by key1, cre_dat, x.iter;

REF_CODE   TYPE  KEY1	KEY2	  LINE_NO TEXT_LINE						  NUM_BY_LINEBREAK NUM_BY_KEY1 CRE_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ---------------- ----------- ---------
O	   OP	 123456 DFLT		1 MY TESTREMARK ON HEADER LINE 1					 1	     1 01-JAN-10
O	   OP	 123456 DFLT		1 ANOTHER TESTREMARK ON HEADER LINE 2					 2	     2 01-JAN-10
O	   OP	 123456 DFLT		2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE			 1	     3 15-JAN-10
O	   OP	 123456 1		1 LINE 1 TESTREMARK 1							 1	     4 01-FEB-10
O	   OP	 123456 1		1 ANOTHER LINE 1 TESTREMARK 2						 2	     5 01-FEB-10
O	   OP	 123456 2		1 LINE 2 TESTREMARK 1							 1	     6 15-FEB-10
O	   OP	 123456 2		1 ANOTHER LINE 2 TESTREMARK 2						 2	     7 15-FEB-10
O	   OP	 123456 1		2 LINE 1 TESTREMARK 3							 1	     8 01-MAR-10

8 rows selected.

SQL> 
SQL> -- now add a few more rows with a different value of KEY1 to see if this query actually works
SQL> -- for more than one KEY1 value
SQL> insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  values
  4  ('O', 'OP', '789012', '1', 1, 'remark line no 1 of key1=789012, key2=1, line_no=1
  5  remark line no 2 of key1=789012, key2=1, line_no=1', 'ME', to_date('1/20/2010','mm/dd/yyyy'), 'ME', to_date('1/31/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  values
  4  ('O', 'OP', '789012', '1', 2, 'remark line no 1 of key1=789012, key2=1, line_no=2
  5  remark line no 2 of key1=789012, key2=1, line_no=2
  6  remark line no 3 of key1=789012, key2=1, line_no=2
  7  remark line no 4 of key1=789012, key2=1, line_no=2', 'ME', to_date('3/20/2010','mm/dd/yyyy'), 'ME', to_date('3/31/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  values
  4  ('O', 'OP', '789012', '2', 1, 'remark line no 1 of key1=789012, key2=2, line_no=1
  5  remark line no 2 of key1=789012, key2=2, line_no=1
  6  remark line no 3 of key1=789012, key2=2, line_no=1', 'ME', to_date('4/20/2010','mm/dd/yyyy'), 'ME', to_date('4/30/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> insert into MPR_TEXT
  2  (REF_CODE, TYPE, KEY1, KEY2, LINE_NO, TEXT_LINE, CRE_USR, CRE_DAT, MOD_USR, MOD_DAT)
  3  values
  4  ('O', 'OP', '789012', '2', 2, 'remark line no 1 of key1=789012, key2=2, line_no=2', 'ME', to_date('4/25/2010','mm/dd/yyyy'), 'ME', to_date('5/1/2010','mm/dd/yyyy'));

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- check the data in the table
SQL> select * from mpr_text order by key1, cre_dat;

REF_CODE   TYPE  KEY1	KEY2	  LINE_NO TEXT_LINE						  CRE_US CRE_DAT   MOD_US MOD_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ------ --------- ------ ---------
O	   OP	 123456 DFLT		1 MY TESTREMARK ON HEADER LINE 1			  ME	 01-JAN-10 ME	  01-FEB-10
					  ANOTHER TESTREMARK ON HEADER LINE 2
O	   OP	 123456 DFLT		2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE	  ME	 15-JAN-10 ME	  15-FEB-10
O	   OP	 123456 1		1 LINE 1 TESTREMARK 1					  ME	 01-FEB-10 ME	  01-MAR-10
					  ANOTHER LINE 1 TESTREMARK 2
O	   OP	 123456 2		1 LINE 2 TESTREMARK 1					  ME	 15-FEB-10 ME	  15-MAR-10
					  ANOTHER LINE 2 TESTREMARK 2
O	   OP	 123456 1		2 LINE 1 TESTREMARK 3					  ME	 01-MAR-10 ME	  01-APR-10
O	   OP	 789012 1		1 remark line no 1 of key1=789012, key2=1, line_no=1	  ME	 20-JAN-10 ME	  31-JAN-10
					  remark line no 2 of key1=789012, key2=1, line_no=1
O	   OP	 789012 1		2 remark line no 1 of key1=789012, key2=1, line_no=2	  ME	 20-MAR-10 ME	  31-MAR-10
					  remark line no 2 of key1=789012, key2=1, line_no=2
					  remark line no 3 of key1=789012, key2=1, line_no=2
					  remark line no 4 of key1=789012, key2=1, line_no=2
O	   OP	 789012 2		1 remark line no 1 of key1=789012, key2=2, line_no=1	  ME	 20-APR-10 ME	  30-APR-10
					  remark line no 2 of key1=789012, key2=2, line_no=1
					  remark line no 3 of key1=789012, key2=2, line_no=1
O	   OP	 789012 2		2 remark line no 1 of key1=789012, key2=2, line_no=2	  ME	 25-APR-10 ME	  01-MAY-10

9 rows selected.

SQL> 
SQL> -- now run the query again
SQL> select ref_code, type, key1, key2, line_no,
  2  	    substr(text_line,
  3  		   case x.iter when 1 then 1 else instr(text_line,chr(10),1,x.iter-1)+1 end,
  4  		   case
  5  		     when instr(text_line,chr(10),1,x.iter) > 0
  6  		     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) + 1 end
  7  		     else length(text_line)
  8  		   end
  9  		  ) text_line,
 10  	    x.iter num_by_linebreak,
 11  	    row_number() over (partition by key1 order by key1, cre_dat, x.iter) num_by_key1,
 12  	    cre_dat
 13    from mpr_text t,
 14  	    ( select level iter
 15  		from dual
 16  	     connect by level <= (select max(length(text_line)-length(replace(text_line,chr(10))))+1 from mpr_text)
 17  	    ) x
 18   where (x.iter <= LENGTH(text_line)-LENGTH(REPLACE(text_line,chr(10)))+1)
 19   order by key1, cre_dat, x.iter;

REF_CODE   TYPE  KEY1	KEY2	  LINE_NO TEXT_LINE						  NUM_BY_LINEBREAK NUM_BY_KEY1 CRE_DAT
---------- ----- ------ ------ ---------- ------------------------------------------------------- ---------------- ----------- ---------
O	   OP	 123456 DFLT		1 MY TESTREMARK ON HEADER LINE 1					 1	     1 01-JAN-10
O	   OP	 123456 DFLT		1 ANOTHER TESTREMARK ON HEADER LINE 2					 2	     2 01-JAN-10
O	   OP	 123456 DFLT		2 AND ANOTHER TESTREMARK ON HEADER BUT A NEW LINE			 1	     3 15-JAN-10
O	   OP	 123456 1		1 LINE 1 TESTREMARK 1							 1	     4 01-FEB-10
O	   OP	 123456 1		1 ANOTHER LINE 1 TESTREMARK 2						 2	     5 01-FEB-10
O	   OP	 123456 2		1 LINE 2 TESTREMARK 1							 1	     6 15-FEB-10
O	   OP	 123456 2		1 ANOTHER LINE 2 TESTREMARK 2						 2	     7 15-FEB-10
O	   OP	 123456 1		2 LINE 1 TESTREMARK 3							 1	     8 01-MAR-10
O	   OP	 789012 1		1 remark line no 1 of key1=789012, key2=1, line_no=1			 1	     1 20-JAN-10
O	   OP	 789012 1		1 remark line no 2 of key1=789012, key2=1, line_no=1			 2	     2 20-JAN-10
O	   OP	 789012 1		2 remark line no 1 of key1=789012, key2=1, line_no=2			 1	     3 20-MAR-10
O	   OP	 789012 1		2 remark line no 2 of key1=789012, key2=1, line_no=2			 2	     4 20-MAR-10
O	   OP	 789012 1		2 remark line no 3 of key1=789012, key2=1, line_no=2			 3	     5 20-MAR-10
O	   OP	 789012 1		2 remark line no 4 of key1=789012, key2=1, line_no=2			 4	     6 20-MAR-10
O	   OP	 789012 2		1 remark line no 1 of key1=789012, key2=2, line_no=1			 1	     7 20-APR-10
O	   OP	 789012 2		1 remark line no 2 of key1=789012, key2=2, line_no=1			 2	     8 20-APR-10
O	   OP	 789012 2		1 remark line no 3 of key1=789012, key2=2, line_no=1			 3	     9 20-APR-10
O	   OP	 789012 2		2 remark line no 1 of key1=789012, key2=2, line_no=2			 1	    10 25-APR-10

18 rows selected.

SQL> 
SQL>

HTH,
tyler_durden
Last edited by durden_tyler; 05-15-2010 at 03:01 PM..
# 16  
Old 05-17-2010
Perfekt, that's it then!

Thanks again for your help Smilie

cheers
Mike
# 17  
Old 05-17-2010
You are welcome. Glad to be of help !

tyler_durden
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