CODEWITHSUNDEEP
sqloracle-databaseplsqlcarriage-return
Avinash
Avinash

Reputation: 55

Display columns that contain a carriage return

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;

Upvotes: 1

Views: 1276

Answers (3)

David Aldridge
David Aldridge

Reputation: 52346

In similar situations I've gone for a Big Hammer and just detected non-printing control codes with a REGEXP_LIKE(col1,'[:cntrl:]'), because next someone will add a tab or something else that breaks the data.

Is it too much to ask that a check constraint be placed on the columns to prevent this from happening?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Plouf
Plouf

Reputation: 627

9th column would be with a case when instr(...) then 1 else 0 end || case when instr(...) then

create table tmp (vc varchar2(20),  vc2 varchar2(20));
insert into tmp values ('abcd','bcda');
insert into tmp values ('bcd','bcda');

select 
  case when instr(vc,'a')>0 then 'col1' else null end ||
  case when instr(vc2,'a')>0 then 'col2' else null end 
from tmp;

As for the second problem, you can just put 'RETURN' in the 10th column. Since you are looking for only one forbidden character and get only lines which have it. When you come up with a solution dealing with several forbidden chars, I'll update.

Upvotes: 1

Related Questions