CODEWITHSUNDEEP
javamysqlregex
DVM
DVM

Reputation: 1238

Select string which contains

This is a very simplified version of what i'm working on but hopefully it will get my point across.

I have a mysql table which looks something like this:

CREATE TABLE Table1(
  ID INT AUTO_INCREMENT,
  Val VARCHAR(50),
     PRIMARY KEY (id)
  );
INSERT INTO Table1 (Val) SELECT "Reference.Int('ABCD')";
INSERT INTO Table1 (Val) SELECT "Reference.Str('EFG','ABC')";
INSERT INTO Table1 (Val) SELECT "Reference.Int('HIJ','EFG','ABC')";

The method i'm working on receives as parameter one of the values in the brackets, for example: "EFG". Is it possible for me to grab all the rows in the table which contain this value. I am aware that if i do something like:

SELECT * from Table1 where Val LIKE "%EFG%"

i can get the right values, my problem is that i need to be more specific because for example one of the values can look something like : 

 Reference.Int('ABCD') + EFGX/200

or

EFG + anything else

Meaning that i need to somehow include this parts also: "Reference.Str()". I don't really care about anything else that is in the brackets, my only concern is to get the ones which contain the value of the parameter i receive.

I was wondering if this could be solved by using REGEXP, but my knowledge of this is weak at best.

Any help is appreciated.

Upvotes: 1

Views: 121

Answers (4)

fthiella
fthiella

Reputation: 49059

I think you could use SUBSTRING_INDEX to get only the part of the strings between brackets:

SUBSTRING_INDEX(SUBSTRING_INDEX(Val, "(", -1), ")", 1)

and then you can use FIND_IN_SET:

SELECT *
FROM Table1
WHERE
  FIND_IN_SET(
    '\'EFG\'',
    SUBSTRING_INDEX(SUBSTRING_INDEX(Val, "(", -1), ")", 1)
  )

See it here.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT * FROM Table1 WHERE Val LIKE "Reference.Str(%'EFG'%"

SQL FIDDLE DEMO

Upvotes: 1

Bohemian
Bohemian

Reputation: 425063

You can use regexp, but there's no need. Just include the quotes surrounding your values in the search term:

select ...
where val like '%\'EFG\'%'

Upvotes: 1

Tyler Eaves
Tyler Eaves

Reputation: 13121

I don't think this is a good fit for a SQL query. You're probably best off creating a second table that stores tokens found in strings, and a reference back to the parent record.

So you could do something like

select record_id from tokens where token_val = 'EFG';

and then

select * from records where record_id in (*results from pervious query)

Basically the idea is to do the hard work up front, ONCE per record, rather than trying to parse on the fly on every query.

Upvotes: 1

Related Questions