Re: How to find a CRLF in a column using SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  Would that be an African or European...?  Err.... ASCII or 
EBCDIC?  That is a question of both the column and the client; it 
may make a difference.

  Possibly just:  WHERE LOCATE(x'0D25', text_column) > 0

<code>
    -- All of below done from an EBCDIC client; i.e. on
    --  the i5 server, from emulated workstation session
    create table qtemp/text (txt varchar(80) ccsid 37)
    ;
    insert into qtemp/text values
     ('Greetings Earthlings!'),(''),('We come in peace.')
    ,(''),('This line too.'),(''),('But not this one.')
    ,(''),('Regards, Aliens')
    -- 9 rows inserted in TEXT in QTEMP.
    ;
    update qtemp/text set txt = replace(txt, 'in', x'0D25')
    where locate('in', txt)>0
    -- 3 rows updated in TEXT in QTEMP.
    ;
    select rrn(T) from qtemp/text T
    where locate(x'0D25', txt)>0
    -- RRNs 1, 3, 5 selected
    ;
    create table qtemp/textu (txt varchar(80) ccsid 1208)
    ;
    insert into qtemp/textu select * from qtemp/text
    -- 9 rows inserted in TEXTU in QTEMP.
    -- Note: CRLF are now x'0D0A' in TEXTU
    ;
    select rrn(U) from qtemp/textu U
    where locate(x'0D25', txt)>0
    -- RRNs 1, 3, 5 selected
    -- I am unclear what the correct search or results
    --  should be here; above just shows what happens.
    -- I expect the ASCII client should use x'0D0A' to
    --  get the same results.
</code>

Regards, Chuck

MKirkpatrick@xxxxxxxxxxxxxxxxx wrote:
> I give up.  I am trying to determine if a column in my table
> contains any CRLFs. 
>
> Any suggestions, using SQL.