Word count for "Like %" pattern in sql query?

Discussion in 'MySQL' started by Tintumol, Feb 21, 2011.

0
  1. #1
    Word count for "Like %" pattern in sql query?



    With that sql query above, I get 2 rows of data, see below...

    The problem I have is I used 3 words, I only want to receive a row with exact number of word. In this case "Ram Pickup 1500" is what I want to receive only. The "Ram Pickup 1500 SRT-10" is a 4 word and I do not want it to come up.

    I have no idea how to do this.

    Thanks...
     
    Tintumol, Feb 21, 2011 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    According to your definition (only 3 words in the model), you need to add a clause to your WHERE query to include only records with 2 spaces like this:

    WHERE
    (
    (UPPER(Model) LIKE '%RAM%') AND
    (UPPER(Model) LIKE '%1500%') AND
    (UPPER(Model) LIKE '%PICKUP%') AND
    (UPPER(Model) LIKE '% % %')

    )
     
    plog, Feb 21, 2011 IP
  3. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #3
    if you explicitly want "Ram Pickup 1500" why are you using 3 Like queries (which is going to be horrible on db efficiency)
     
    AstarothSolutions, Feb 22, 2011 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Unless you are using _cs on your tables, you shouldn't need to use upper. Databases don't care about case unless you tell them to. If the collation of your table/column ends in _ci, upper isn't needed. Huge overhead to use upper if you don't need it.
     
    jestep, Feb 23, 2011 IP
  5. carleisenstein

    carleisenstein Peon

    Messages:
    253
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I don't think plog's solution works, because it would also pick up four or five word answers. There's probably a more efficient way, but I've found this to work in the past:

    WHERE LENGTH(Model)=LENGTH(REPLACE(Model, ' ', ''))-3

    It's really messy though - instead I'd recommend that if word counts are really important for you, you populate them in your database when entries are added / ammended. Or that you have a batch process to populate the word counts on a regular basis.
     
    carleisenstein, Feb 25, 2011 IP
  6. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #6
    carleisenstein is right--my initial solution would probably pick up more than it should. I think adding one more condition to the WHERE clause would do it:

    
WHERE
(
(UPPER(Model) LIKE '%RAM%') AND
(UPPER(Model) LIKE '%1500%') AND
(UPPER(Model) LIKE '%PICKUP%') AND
(UPPER(Model) LIKE '% % %') AND 
(UPPER(Model) NOT LIKE '% % % %') 
)
    PHP:
    The last one would kick out any where the model is more than 3 words.
     
    plog, Feb 28, 2011 IP