SQL Query to Search for String within a String (but the string may be separated)

Question

I can't figure out the SQL query that would be able to select a row based on a column containing a string but the string could be separated.

For example, the string I am searching for:

Would match:

That includes 3 things:

1) Any string before

2) Any string after

3) Any string randomly in the middle

I have tried using the following queries

SELECT * FROM Products WHERE FileName LIKE %1345%

SELECT * FROM Products WHERE CONTAINS(FileName, '1345')

But these will not select if the string is randomly in the middle. What should I be doing instead?

@AvinashRaj is that using LIKE? And also, of course, the string would not be simple numbers so separating the characters like that could be difficult? — Jed Boffey
– Jed Boffey, Commented Feb 15, 2015 at 5:15

josegomezr · Accepted Answer · 2015-02-15 07:01:01Z

2

You'd need to pre-prepare for regexp match, so you produce a query like this

SELECT * from name1
WHERE name1.name REGEXP ".*a.*p.*a.*"

answered Feb 15, 2015 at 7:01

josegomezr

9265 silver badges16 bronze badges

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

2 Comments

Jed Boffey Over a year ago

I don't understand how to implement this?

josegomezr Over a year ago

in order to match strings randomly in the middle of your criteria, you need to pre-prepare your criteria BEFORE sending the query using your favorite server side language, you'd append .* before and after each character so REGEXP operator works as expected. Just remember to escape user input tu prevent SQL attacks.

Wiktor Stribiżew · Accepted Answer · 2015-02-17 08:26:28Z

0

This is a working solution tested in MySQL Workbench:

SELECT * FROM Products WHERE FileName LIKE '%1%2%3%';

It matches entries like

2004267695, 2005285881, 20060004841, 20060136398, 20080189084, and 20090189887.

Folio 8 1/2 x 13 in

1992-2003

answered Feb 17, 2015 at 8:26

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Collectives™ on Stack Overflow

SQL Query to Search for String within a String (but the string may be separated)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related