CODEWITHSUNDEEP
jsonpostgresql
Jsy
Jsy

Reputation: 53

How to have a whole string as a normal string in PostgreSQL's regular match?

In my sql code, I receive a input string as a regular match's filter, I want to have the whole string as a normal string, even it includes some special characters.

Just look below:

do $$ declare 
    jdata jsonb='[{"name":"Dog 3*240+1*120"}]'::jsonb;
    vfilter1 text='dog';
    vfilter2 text='3*240+1*120';
    vexists bool=false;
begin 
    select jdata @? concat('$[*] ? (@.name like_regex "',vfilter1,'" flag "i")')::jsonpath into vexists;
    raise notice 'exists:%',vexists; --the result is true
    select jdata @? concat('$[*] ? (@.name like_regex "',vfilter2,'" flag "i")')::jsonpath into vexists;
    raise notice 'exists:%',vexists;-- the result is false
end;
$$ language plpgsql;

the sql code

the string 3*240+1*120 include + and * characters, perhaps this causes the regular match have them as special character. In my code, I just want to have the whole vfilter string includes all special characters together as a normal string for the regular match.

What should I do?

Upvotes: 0

Views: 183

Answers (1)

jjanes
jjanes

Reputation: 44305

You should read the documentation for the feature you are using.

The optional flag string may include one or more of the characters i for case-insensitive match, m to allow ^ and $ to match at newlines, s to allow . to match a newline, and q to quote the whole pattern (reducing the behavior to a simple substring match).

Upvotes: 3

Related Questions