MySQL. How do you find a word in the line?



  • In OBD:

        id   vstavki
        1  фианит,топаз, бриллиант
        2  гранат,аметист
        3  раухтопаз
        4  аметист, топаз
        5  london-топаз, фианит
    

    My request is:

    SELECT * FROM price 
    WHERE section=1 
    AND price BETWEEN 600 AND 1500000 
    AND size IN(16,16.5,17) 
    AND material IN("Золото","Серебро") 
    AND (vstavki LIKE "%Бриллиант%" OR vstavki LIKE "%Топаз%") 
    group by nomer
    

    However, the conclusion is that there is rauchtopaz. How do you write a request so that the rauchtopaz doesn't come into a topaz sample?



  • In fact:

    select *
      from price
     where lower(vstavki) regexp '(^|[^a-zа-яр-я])(топаз|бриллиант)([^a-zа-я]|$)';
    

    But, please, I am in the first square brackets excluding any letters before the words except the full range. а-я insert р-я without that, he'd always found a ragtopaz because he thought the "x" was not in the range. а-я, but it pretty well understood that it's in range. р-я (I have piloted the range). I don't know. But since he had one glitch with Russian letters, maybe some other thing. I'm not going to give 100% assurance that he'll work with arbitrary words.

    I used it in any case. lower The row to the small letters, so the words sought in regular terms should be as small as the can be applied. lower

    Besides, you might want to adjust the ranges of letters-of-exclusions to your understanding, which should fall under the notion of a slit word, and that's not.

    In fact, it's a reason to think about the structure of the base, at least when this field is filled, it's not possible to write anything, but to ask each material separately and to form the line with the right dividers. Although, if not at all, there should be a directory of admissible boxing materials and a price plate with separate entries on the line price.

    And maybe you should look towards a full-text search of sphinx or at least. http://www.mysql.ru/docs/man/Fulltext_Search.html




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2