SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code MySQL:
    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:
    Code MySQL:
    --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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you are right -- the database design is "that much horrible"

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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%'

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what happened when you tested it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •