Display columns that contain a carriage return

Question

I have a Students table, which contains 7 address fields.

I need to display 1 row each for student where the address fields have carriage return, if any.

It's confused after this.

The 9th column (1st column - Student ID, 2-8 column - 7 address fields) must contain the list of column names which have a carriage return ( like addr_1, addr_3, 1 for each student ID separated by a comma)

The 10th column must contain the illegal character (in this case, carriage return).

This code must be further extended to other illegal characters identified now and then and a report has to be generated.

I am unable to work on 9th and 10th columns. Can anyone help?

SELECT pty.id,
a.addr_1,
a.addr_2,
a.addr_3,
a.addr_4,
a.addr_5,
a.addr_6,
a.addr_7
FROM addr a 
inner join contact cON a.idf = c.add_idf
inner join pty ON c.pty_id = pty.id
WHERE 
INSTR(a.addr_1,CHR(13)) > 0 OR
INSTR(a.addr_2,CHR(13)) > 0 OR
INSTR(a.addr_3,CHR(13)) > 0 OR
INSTR(a.addr_4,CHR(13)) > 0 OR
INSTR(a.addr_5,CHR(13)) > 0 OR
INSTR(a.addr_6,CHR(13)) > 0 OR
INSTR(a.addr_7,CHR(13)) > 0;

Answer 1

This sounds like a homework question. So, let me give you some hints:

(1) You can generate a table using syntax, such as:

select chr(13) as badchar from dual union all
select '!' . . .

(2) You can cross join this into the table and use a very similar where clause.

(3) You can then select the bad character from the table.

(4) You'll need an aggregation.

Actually, I would be inclined to drop the requirement of one row per student and instead have one row per student/bad character. Here is an approach:

select a.id,
       a.addr_1, a.addr_2, a.addr_3, a.addr_4, a.addr_5, a.addr_6, a.addr_7,
       ((case when INSTR(a.addr_1, b.badChar) > 0 then 'addr_1,' else '' end) ||
        (case when INSTR(a.addr_2, b.badChar) > 0 then 'addr_2,' else '' end) ||
        (case when INSTR(a.addr_3, b.badChar) > 0 then 'addr_3,' else '' end) ||
        (case when INSTR(a.addr_4, b.badChar) > 0 then 'addr_4,' else '' end) ||
        (case when INSTR(a.addr_5, b.badChar) > 0 then 'addr_5,' else '' end) ||
        (case when INSTR(a.addr_6, b.badChar) > 0 then 'addr_6,' else '' end) ||
        (case when INSTR(a.addr_7, b.badChar) > 0 then 'addr_7,' else '' end)
       ) as addrs,
       b.badChar
from a cross join
     (select chr(13) as badChar from dual) as b
WHERE INSTR(a.addr_1, b.badChar) > 0 OR
      INSTR(a.addr_2, b.badChar) > 0 OR
      INSTR(a.addr_3, b.badChar) > 0 OR
      INSTR(a.addr_4, b.badChar) > 0 OR
      INSTR(a.addr_5, b.badChar) > 0 OR
      INSTR(a.addr_6, b.badChar) > 0 OR
      INSTR(a.addr_7, b.badChar) > 0;

It leaves an extra comma at the end of the column names. This can be removed by making this a subquery and doing string manipulations at the next level.

To put all badchars on one line would require an aggregation. However, I am not clear what the 9th and 10th columns would contain in that case.