REGEXP_SIMILAR - To identify new line characters

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

REGEXP_SIMILAR - To identify new line characters

 

Hi,

 

How to identify if a column has new line characters or not using REGEXP_SIMILAR.

There is a column in my table which has new line characters but when I use as mentioned below it is not working ..

 

    REGEXP_SIMILAR(<COL>,X'0A')=1

 

Please suggest

4 REPLIES
Teradata Employee

Re: REGEXP_SIMILAR - To identify new line characters

POSITION('0A'xc IN <col>) > 0 would likely be a better solution in this case.

If you really want to use REGEXP_SIMILAR, then the pattern must be something that will match the entire column value, e.g. '.*'||'0A'xc||'.*'

Enthusiast

Re: REGEXP_SIMILAR - To identify new line characters

 

Thanks Fred for the details.

 

Both are working but i am getting different count when querying on same table.

Highlighted
Teradata Employee

Re: REGEXP_SIMILAR - To identify new line characters

Then for REGEXP_SIMILAR, specify the 'n' option as the third parameter - so the pattern can match strings with more than one "new line".

Enthusiast

Re: REGEXP_SIMILAR - To identify new line characters

 

Thanks Fred it is working fine now ..getting same count