Mysql Question posted in
The official MySQL documentation can be found here.

MySQL regular expression query to check if string is contained outside brackets

I want a regular expression which I can use on MySQL.

  • The string I’m looking for must be contained outside the brackets.
  • brackets don’t have nesting.(if exists, I don’t care result)
  • brackets have beginning and ending.(if exists, I don’t care result)

Let be "3" the word I’m looking for. This changes every time.

– target string result
1 [123] not match
2 123 match
3 [123]123 match
4 not match
5 [123][123] not match
6 [123]123[123] match
7 [[123]] not exist (nesting)
8 123[123 not exist (no ending) 
create table my_table(id integer,text varchar(128));
insert into my_table(id,text) values 
(1,"[123]"),
(2,"123"),
(3,"[123]123"),
(4,""),
(5,"[123][123]"),
(6,"[123]123[123]"),
(7,"[[123]]"),
(8,"123[123");
select * from my_table where text REGEXP "3";
Tags:

2

Answers


  1. 0

    You can try this !

    For REGEXP :

    SELECT * 
FROM my_table 
WHERE text REGEXP '(^|[^\[][^\]]*)3([^\[][^\]]*|$)';

    For Sql Server:

    SELECT *
FROM my_table
WHERE CHARINDEX('3', text) > 0
AND (
    CHARINDEX('3', text) < CHARINDEX('[', text)
    OR CHARINDEX('3', text) > CHARINDEX(']', text)
    OR CHARINDEX('[', text) = 0 
    OR CHARINDEX(']', text) = 0 
);
    Login or Signup to reply.
  2. 0

    Here’s the trick, I used subquery to remove all those fields with valid [text] format, then applied a filter.

    select t1.res from (
select id, regexp_replace(text, '\[[^]]*\]', '') res
from my_table) t1
where locate(']', t1.res)  = 0 && locate('[', t1.res)  = 0 and ifnull(t1.res, '') != ''
and t1.res REGEXP '3'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search