CODEWITHSUNDEEP
sqloracle-database
SQL_STRIKER
SQL_STRIKER

Reputation: 5

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).

Upvotes: 0

Views: 1012

Answers (2)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 1

bi_noob
bi_noob

Reputation: 107

try matching for regular expression for new line, this query should result only those records where you have address spread across more than 1 lines

SELECT * 
FROM XRAY_TEST 
where ADD_LI1 regexp '(\n)';

Upvotes: 0

Related Questions