CODEWITHSUNDEEP
phpmysqlsearch
user1100149
user1100149

Reputation: 266

Search for characters within a string with MySQL

Let's say, for example, that you have a random string in the DB: APKHDP

I want to search all strings that contain the characters PHP (in that order). So I search for PHP and it returns the above string. Or I search for HPP, and it returns nothing.

Is there a simple solution to make this possible, perhaps using REGEX? It sounds very simple. But the asnwers I am finding are massively complex so far.

I am using this with PHP (hence the example!), and I'm happy to integrate PHP into the solution if it's the best way forward.

Upvotes: 2

Views: 3996

Answers (3)

Codecraft
Codecraft

Reputation: 8296

Use a MySQL regular expression - you can match things, but can't replace - for example:

SELECT 'APKHDP' REGEXP('[PHP]')

Returns '1' (ie, the regexp matched the string).

Although the regular expression above may not be correct for your needs (it matches any use of the letters P, H and P) so you'd have to tweak that.

You could make your regex more complicated, or you could do multiple matches:

SELECT field FROM TABLE WHERE field REGEXP(match1) AND field REGEXP(match2)..etc

Whichever seems simplest to you...

Link: MySQL Manual for REGEXP

Upvotes: 2

xdazz
xdazz

Reputation: 160853

SELECT * FROM table WHERE text_field REGEXP '.*P.*H.*P.*'

The doc.

Upvotes: 3

Sathishkumar
Sathishkumar

Reputation: 3404

SELECT * 
FROM  `COLUMNS` 
WHERE  `TABLE_NAME` 
REGEXP  'APKHDP'

Upvotes: 0

Related Questions