How to get matched string part in SQL statement with many like checkes

Question

Is it possible, to get matched string part in sql statement, which has many like statements.

SELECT fieldname
FROM table
WHERE content LIKE '%$val_1%'
   OR content LIKE '%$val_2%'
   OR content LIKE '%$val_3%'
   .
   .
   .
   OR content LIKE '%$val_n%'

i.e i need to get all that values($val_1, or(and) $val_2 ...), which matched in content.

example

if my content is the following

content = 'this is my testing text'

and i have the following values, to check in like statements

$val_1 = 'thi';
$val_2 = 'esting';
$val_3 = 'other value, which not in my content';

I need to get the matched partts, i.e "thi" and "esting", because they are matched in my content.

I don't get your question.....your query will run, if that is what you are asking — Lamak
– Lamak, Commented Jan 30, 2012 at 20:44
It sounds like you want to match the pattern val_[0-9]? If that's so, take a look at: dev.mysql.com/doc/refman/5.0/en/pattern-matching.html - RLIKE is probably what you are after. — dash
– dash, Commented Jan 30, 2012 at 20:47
I need to get one of ($val_1,...$val_n) values(or some of them), which cause my query to run. i.e that values, which are in my content. — Simon
– Simon, Commented Jan 30, 2012 at 20:47
Are you asking how to find which $val_* was matched in content? — nachito
– nachito, Commented Jan 30, 2012 at 20:48
I believe that @Syom is asking how to get which like operator(s) resulted in the match. For example, did "content LIKE '%$val_1%'" evaluate to true? or did "content LIKE '%$val_2%'"? etc. — Adam Porad
– Adam Porad, Commented Jan 30, 2012 at 20:49

Cal · Accepted Answer · 2012-01-30 23:18:27Z

2

Since you say you're doing this in PHP, just use PHP to find out which bits matched after you get your rows back:

if (stripos($row['content'], $val_1) !== false) this bit matched!
if (stripos($row['content'], $val_2) !== false) this bit matched!
if (stripos($row['content'], $val_3) !== false) this bit matched!

edited Jan 30, 2012 at 23:18

answered Jan 30, 2012 at 20:53

Cal

7,13227 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nachito Over a year ago

stripos() would be more appropriate

Malk · Accepted Answer · 2012-01-30 20:49:29Z

1

SELECT fieldname, matchpart = '$val_1'
FROM table
WHERE content LIKE '%$val_1%'
UNION 
SELECT fieldname, matchpart = '$val_2'
FROM table
WHERE content LIKE '%$val_2%'
...
UNION 
SELECT fieldname, matchpart = '$val_n'
FROM table
WHERE content LIKE '%$val_n%'

answered Jan 30, 2012 at 20:49

Malk

12k4 gold badges35 silver badges34 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:03:32Z

1

The following query will return the column fieldname and condX where condX == 1 iff content LIKE '%$val_X%', else 0.

Originally from https://stackoverflow.com/a/2261001/961455:

SELECT fieldname,
    `content` LIKE '%$val_1%' AS cond1,
    `content` LIKE '%$val_2%' AS cond2,
    ... ,
   `content` LIKE '%$val_n%' AS condn
FROM table
HAVING cond1 OR cond2 OR ... OR condn

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Jan 30, 2012 at 20:52

nachito

7,0352 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to get matched string part in SQL statement with many like checkes

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related