Auto increment counter if field length is greater than n

Here is a solution that makes no prior assumptions about the length of comments. I added two strings, one short (less than 5 characters) and one longer than 10 characters, as well as one with NULL comment to make sure the row is not lost, to test the solution thoroughly.

I assume (empid, dt) is a unique combination (perhaps primary key) in the base table. By the way, I hope you are not actually using Date as a column name in your schema.

The solution does not contain the WITH clause; it begins at select empid... The ORDER BY clause may not be needed either.

with     test_data ( empid, empname, servicenumber, dt, comments ) as (       select 1, 'a',  123, to_date('23-03-1990', 'dd-mm-yyyy'), 'wednesday'   from dual union all       select 1, 'a', 1234, to_date('24-04-1990', 'dd-mm-yyyy'), 'Test12'      from dual union all       select 2, 'b',  234, to_date('24-05-2016', 'dd-mm-yyyy'), 'Todayis'     from dual union all       select 2, 'b',  235, to_date('25-05-2016', 'dd-mm-yyyy'), 'Joe'         from dual union all       select 3, 'c',  238, to_date('25-05-2016', 'dd-mm-yyyy'), ''            from dual union all       select 4, 'c', 2238, to_date('25-05-2016', 'dd-mm-yyyy'), 'longer string' from dual     )select empid, empname, servicenumber, dt, level as comno,       substr(comments, 5 * level - 4, 5) as commentsfrom   test_dataconnect by level <= 1 + length(comments) / 5     and   prior empid = empid     and   prior dt    = dt     and   prior sys_guid() is not nullorder by empid, dt;     EMPID E SERVICENUMBER DT              COMNO COMMENTS---------- - ------------- ---------- ---------- --------------------         1 a           123 1990-03-23          1 wedne         1 a           123 1990-03-23          2 sday         1 a          1234 1990-04-24          1 Test1         1 a          1234 1990-04-24          2 2         2 b           234 2016-05-24          1 Today         2 b           234 2016-05-24          2 is         2 b           235 2016-05-25          1 Joe         3 c           238 2016-05-25          1         4 c          2238 2016-05-25          1 longe         4 c          2238 2016-05-25          2 r str         4 c          2238 2016-05-25          3 ing

Added: If your data is of CLOB data type, it is better to use the dbms_lob version of substr. Also, if you must chop the data into 75 character segments, you must adjust several numbers. Here is the same solution, with these two changes and without the ORDER BY clause (not needed if this is used to migrate the data to another db product). NOTE: dbms_lob.substr() has the "amount" and the "offset" (second and third arguments) reversed compared to the usual substr() function; notice that when you compare the solutions.

select empid, empname, servicenumber, dt, level as comno,       dbms_lob.substr(comments, 75, 75 * level - 74) as commentsfrom   test_dataconnect by level <= 1 + length(comments) / 75     and   prior empid = empid     and   prior dt    = dt     and   prior sys_guid() is not null;

Here is one way to do this:

with n as (      select 1 as n from dual union all      select 2 from dual     )select EmpID, EmpName, ServiceNumber, Date, ComNo,       substr(comments, n.n * 5 - 4, 5) as Commentsfrom t join     n     on length(comments) >= n.n * 5 + 1;

Note: Your example only has comments up to 10 characters, so only the values 1 and 2 are needed for n. You can create additional rows by expanding n.

A brute force option would be to just UNION together the records resulting from splitting the comments by 5 characters at a time:

SELECT EmpID, EmpName, ServiceNumber, Date, 1, SUBSTR(Comments, 1, 5) AS CommentsFROM yourTableUNION ALLSELECT EmpID, EmpName, ServiceNumber, Date, 2, SUBSTR(Comments, 6, 5) AS CommentsFROM yourTableWHERE SUBSTR(Comments, 6, 5) <> ''  -- but don't show a record ifUNION ALL                           -- all characters already used...