[Info-ingres] Similar to what exactly

[Martin Bowes]

Yes. You can do similar to '%[^' + x'09' + x'0a' + x'0d' + '[:print:]]%';

That hex string supplied is so ugly it's a thing of rare beauty.

Marty

From: Paul White [mailto:paul.white at shift7solutions.com.au]
Sent: 30 August 2018 11:18
To: Martin Bowes; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] Similar to what exactly

Hi Marty,
Looks a bit like an SQL injection hack.
Are tabs (x09) allowed also?

try...
similar to  x'255b002d080b0c0e2d1f7f2dff5d25'


Paul

From: info-ingres-bounces at lists.planetingres.org [mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin Bowes
Sent: Thursday, 30 August 2018 6:45 PM
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Similar to what exactly

Yes it can!

insert into search_pattern values('%[^' + x'0a' + x'0d' + '[:print:]]%');

select id, a_string from test_search a, search_pattern b
where a.a_string similar to b.pattern;

And it works!

Bwahahaha

Marty

From: Martin Bowes
Sent: 30 August 2018 09:26
To: Martin Bowes; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] Similar to what exactly

Got it with a ceaseless five minutes of hacking!

select id, a_string from test_search
where a_string similar to '%[^' + x'0a' + x'0d' + '[:print:]]%';

I didn't know you could build up search expressions like that!

I wonder if I could store the search expression in a column?

Marty

From: Martin Bowes [mailto:martin.bowes at ndph.ox.ac.uk]
Sent: 30 August 2018 09:07
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] Similar to what exactly

Hi All,

My boss (bless his little cotton socks) has asked me to find strings which contain any non printing characters.

Sadly the 'contain' predicate doesn't allow any fancy pattern matching so we have to do this using the 'similar to' predicate.

So I came up with:

select id, a_string from test_search
where a_string similar to '%[^[:print:]]%';

And that works well.

But now he says, in some columns he'd like to allow it to have the characters \n (x'0a') and \r (x'od').

Okay then...

This works:
select id, a_string from test_search
where replace(replace(a_string, x'0d', ''), x'0a', '') similar to '%[^[:print:]]%';

But that means on each row I have to call the replace function twice and then apply the similar to pattern. That doesn't sound terribly efficient to me.

What I'd like to do is extend my character set to be '%[^x'0a'x'od'[:print:]]%' ... but pretty clearly that syntax isn't going to fly.

Anyone got any ideas?

Martin Bowes




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180830/5e0f259d/attachment.html>