Forum Discussion

Shivshankar's avatar
Shivshankar
Ace
8 years ago

Like query in LiQL

Hi !

 

I am trying to fetch subject of all messages in particular board with certain keyword , formed this LiQL query

 

SELECT id,subject FROM messages WHERE board.id = '4' and subject MATCHES 'Batch'

The problem is , this "MATCHES" gives result of only those subject which have exact "Batch" as a word in them and won't give subject containing "BatchUpdate" as result . I want something which works similar to LIKE function in MYSQL for other database query languages.

Can someone provide with some kind of input to achieve this.

 

  • VikasB's avatar
    VikasB
    8 years ago

    Hi srujanayeruvaka  
    Did you notice it would not work if you are not using the braces surrounding to the text which RobertT missed in their post?  But it works fine with braces.

    SELECT * FROM messages WHERE board.id = 'XYZ' and subject MATCHES ('batch*')

    Shivshankar  have a look, it really works.

  • RobertT's avatar
    RobertT
    Boss
    8 years ago

    Shivshankar

     

    I've tried a similar query to yours in one of my environments and found that it works properly finding variations as you'd expect. One thing to note though is your syntax, try modifying the query so that AND is uppercase and adding an asterisk wildcard to the end of the string you're matching.

     

    For reference I ran the following test in my environment 

    SELECT id,subject FROM messages WHERE subject MATCHES 'test*'

    and it returned messages with test, tests, testing, TestUpdate etc in the subject so it does work.

     

    I hope this helps, 

     

     

    • Shivshankar's avatar
      Shivshankar
      Ace
      8 years ago

      Hi,

       

      I tried as you have  specified, i tried with following queries.

      Following are attached screenshots of the queries i have run ,with file name same as index of query. I didn't find any changes in search results putting * wildcard at last. Another query with 'batchupdate' search is giving results , and as 'batchupdate' contains 'batch' so second query should have given some changes in count. 1 without * wildcard2 with * wildcard at end3 with * wildcard at end and beigining4 batchUpdate search

       

      1) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batch' AND depth=0 limit 1000 offset 0
2) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batch*' AND depth=0 limit 1000 offset 0
3) select count(*) from messages WHERE board.id='4' AND subject MATCHES '*batch*' AND depth 0 limit 1000 offset 0
4) select count(*) from messages WHERE board.id='4' AND subject MATCHES 'batchupdate' AND depth 0 limit 1000 offset 0

       

      • VikasB's avatar
        VikasB
        Boss
        8 years ago

        Hi Shivshankar  You can also pass the multiple keywords in the query.

        select subject from messages WHERE subject MATCHES ('batch', 'batchupdate') AND depth=0 limit 1000 offset 0

        If you will use the below query it will fetch the messages only having subjects like batch, batches, batching etc but not batchupdate. 

        select subject from messages WHERE subject MATCHES 'batch'' AND depth=0 limit 1000 offset 0
  • sh030348's avatar
    sh030348
    Adept
    4 years ago

    srujanayeruvaka  i saw this discussion and i know it is a very old topic but i am facing a similar issue and has been trying for a long time to get it to work.I have below query and i have tried all the suggestions provided with combination and do not get any result.I do have the data though.Could you please help thanks in advance.

    SELECT id,body FROM messages WHERE body matches ('6246723261001')

    SELECT id,body FROM messages WHERE body matches ('6246723261001*')

    SELECT id,body FROM messages WHERE body matches ('*6246723261001*')

    data:

    SELECT id,body FROM messages WHERE id='483'

     

  • srujanayeruvaka's avatar
    srujanayeruvaka
    Champion
    4 years ago

    Hi sh030348 - Matches keyword sometimes doesn't work as expected. I would suggest you to file a support case with Khoros and report your use case.