SELECT IS NULL('abc' LIKE '%bc%')

[js_11]

Hey,
I have a little bit stupid problem because of the database desing. I need to do a string matching comparison with search terms and a value that I get from php script.

So, normaly I would do:

SELECT a FROM b WHERE a LIKE '$search%'

But now, I need to strip off some parts of the a-field before LIKE comparison.

And I think I can't do that stripping in MySQL as there is no "preg_replace" in MySQL.

I had this idea to use a "simulated" query in MySQL, but it seems this is not possible:

--abc is string I get from php preg_replace
--basically I just want to test if MySQL's LIKE operator will find a match or not with those values generated by me
SELECT IS NULL('abc' LIKE 'bc%')

But I would like to hear if that "simulated" query did not work cause of some syntax error or if it did not work cause it is not possible at all in MySQL.

Thanks very much.

[r937]

that syntax is invalid

what parts of the column value do you need to strip off?

you're right, REGEXP in mysql can only find, not replace

but there might be other ways

[js_11]

The value in that column may contain something like:
"stuf here...<special_marker>other stuff...<special_marker>final stuff here..."

And now I need to match from the part that is in bold in my above exaple. I can't use SUBSTR cause string lenght may vary and the problem is that "the last part" after the second <special_marker> may contain stuff that gets matched, which is incorrect.

And in some situations, that <special_marker> may not be at all in the text that the field holds. So I don't think it is possible to do with REGEXP either.

I know regular LIKE comparison would be perfect for this, but too bad the database design is that much horrible that it will produce matches that should not be found.

[r937]

you are right -- the database design is "that much horrible"

try the SUBSTRING_INDEX function -- it can find substrings based on the special marker

[js_11]

Ah yeah, I tested it briefly and it does seem to work correctly!

However, I couldnt manage to create such a demo situation, where there are no <special_markers>'s at all in the field. How would it work in such a situation?

I'm using it like this now:
WHERE SUBSTRING_INDEX(specials, '<special_marker>', 2) LIKE '%$search%'

[r937]

what happened when you tested it?

[js_11]

Ah right, I manually tested it with data with no <special_marker> and it seemed to work correctly!

Looks very very good. But of course I'm not entirely sure if I can produce correct test with that late at afternoon

But thank you very much, I think I let it be that way and continue checking tomorrow.