Skip to content
Advertisement

Identify a column where data has been entered multi line

Oracle Database 11g Enterprise 11.2.0.4.0, PL/SQL Release 11.2.0.4.0

I have a Table, which contains an address column (NVARCHAR2(50)). This column is supposed to be line 1 of the address only.

There are a small few records where the address was entered incorrectly into the column, this is supposed to be the first line of the address but it seems through the application a user can enter the first line of the address multiple times, or even the full address in the first line of this address column.

Screenshot1

Expanding record1 (Double clicking the column in Toad) will show this. Note the extra lines.

screenshot 2

I need to write SQL which will identify columns that contain the first line of the address entered in this incorrect format (e.g. multiple lines or tabbed etc.) I found this on another stack exchange post but it doesn’t help with my multiple line situation.

select * from tablename where regexp_like (columnname, chr(9));

I wrote this SQL to repro and it seems to demo my scenario.

CREATE TABLE XRAY_TEST (ADD_LI1 NVARCHAR2(50));

INSERT INTO XRAY_TEST (ADD_LI1)
VALUES ('25 CAMBRIDGE STREET
25 CAMBRIDGE STREET
25 CA');
COMMIT;

INSERT INTO XRAY_TEST (ADD_LI1)
VALUES ('25 WESTWIND CRESCENT');
COMMIT;

SELECT *
FROM XRAY_TEST

So to summarize I want to be able to identify any record in the table that has been entered like record1 (e.g. multiline).

Advertisement

Answer

Seems like you are looking for values that contain linefeeds, so:

where somecol like '%'||chr(10)||'%'

Or alternatively,

where instr(somecol, chr(10)) > 0

A regex here would be overkill.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement