SQL Query not returning desired result with NOT LIKE










0














I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :



SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR `multi_vendorId` LIKE '%,176%'
OR `multi_vendorId` LIKE '%,176,%'
OR `multi_vendorId` LIKE '%176,%'
OR `multi_vendorId` LIKE '%[176]%'
AND (`excluded_branch_id` NOT LIKE '%,203%' OR `excluded_branch_id` NOT LIKE '%,203,%' OR `excluded_branch_id` NOT LIKE '%203,%' OR `excluded_branch_id` NOT LIKE '%[203]%' )


And here is the result:
screenshot



Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.



Please help!










share|improve this question





















  • Try with not like [203]
    – Himanshu Ahuja
    Nov 12 at 18:52











  • the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
    – Himanshu Ahuja
    Nov 12 at 18:56










  • What is with [203]? If there is a single value then you store it wrapped inside ?
    – Salman A
    Nov 12 at 19:23










  • That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
    – hyd00
    Nov 12 at 19:27















0














I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :



SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR `multi_vendorId` LIKE '%,176%'
OR `multi_vendorId` LIKE '%,176,%'
OR `multi_vendorId` LIKE '%176,%'
OR `multi_vendorId` LIKE '%[176]%'
AND (`excluded_branch_id` NOT LIKE '%,203%' OR `excluded_branch_id` NOT LIKE '%,203,%' OR `excluded_branch_id` NOT LIKE '%203,%' OR `excluded_branch_id` NOT LIKE '%[203]%' )


And here is the result:
screenshot



Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.



Please help!










share|improve this question





















  • Try with not like [203]
    – Himanshu Ahuja
    Nov 12 at 18:52











  • the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
    – Himanshu Ahuja
    Nov 12 at 18:56










  • What is with [203]? If there is a single value then you store it wrapped inside ?
    – Salman A
    Nov 12 at 19:23










  • That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
    – hyd00
    Nov 12 at 19:27













0












0








0







I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :



SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR `multi_vendorId` LIKE '%,176%'
OR `multi_vendorId` LIKE '%,176,%'
OR `multi_vendorId` LIKE '%176,%'
OR `multi_vendorId` LIKE '%[176]%'
AND (`excluded_branch_id` NOT LIKE '%,203%' OR `excluded_branch_id` NOT LIKE '%,203,%' OR `excluded_branch_id` NOT LIKE '%203,%' OR `excluded_branch_id` NOT LIKE '%[203]%' )


And here is the result:
screenshot



Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.



Please help!










share|improve this question













I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :



SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR `multi_vendorId` LIKE '%,176%'
OR `multi_vendorId` LIKE '%,176,%'
OR `multi_vendorId` LIKE '%176,%'
OR `multi_vendorId` LIKE '%[176]%'
AND (`excluded_branch_id` NOT LIKE '%,203%' OR `excluded_branch_id` NOT LIKE '%,203,%' OR `excluded_branch_id` NOT LIKE '%203,%' OR `excluded_branch_id` NOT LIKE '%[203]%' )


And here is the result:
screenshot



Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.



Please help!







mysql sql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 12 at 18:42









hyd00

62321029




62321029











  • Try with not like [203]
    – Himanshu Ahuja
    Nov 12 at 18:52











  • the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
    – Himanshu Ahuja
    Nov 12 at 18:56










  • What is with [203]? If there is a single value then you store it wrapped inside ?
    – Salman A
    Nov 12 at 19:23










  • That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
    – hyd00
    Nov 12 at 19:27
















  • Try with not like [203]
    – Himanshu Ahuja
    Nov 12 at 18:52











  • the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
    – Himanshu Ahuja
    Nov 12 at 18:56










  • What is with [203]? If there is a single value then you store it wrapped inside ?
    – Salman A
    Nov 12 at 19:23










  • That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
    – hyd00
    Nov 12 at 19:27















Try with not like [203]
– Himanshu Ahuja
Nov 12 at 18:52





Try with not like [203]
– Himanshu Ahuja
Nov 12 at 18:52













the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
– Himanshu Ahuja
Nov 12 at 18:56




the main reason is restid is same for both so it is meeting the condition as restid=176 and (since inside it is OR so if any of them mets ) then it will result
– Himanshu Ahuja
Nov 12 at 18:56












What is with [203]? If there is a single value then you store it wrapped inside ?
– Salman A
Nov 12 at 19:23




What is with [203]? If there is a single value then you store it wrapped inside ?
– Salman A
Nov 12 at 19:23












That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
– hyd00
Nov 12 at 19:27




That's supposed to also have multiple values e.g. [203,176,345] ... We have wrapped in square brackets just for ease when retrieving in PHP in form of Array. I understand now its not a good practice but that's how the existing system has been running and handed over to me
– hyd00
Nov 12 at 19:27












3 Answers
3






active

oldest

votes


















1














You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:



(excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')


E.g:



SELECT
'203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
'111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
'1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
'1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0





share|improve this answer






















  • Works like a charm! Thanks a lot Salman for reminding me of REGEXP
    – hyd00
    Nov 12 at 19:31


















1














Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.



Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.



But, the logic would be simplified if you used find_in_set(). I think you intend:



WHERE (`restId` = '176' OR branchId = '203' OR
find_in_set(176, `multi_vendorId`)
) AND
(find_in_set(203, `excluded_branch_id`)





share|improve this answer






















  • Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
    – hyd00
    Nov 12 at 18:59











  • @hyd00 . .. I answered your question.
    – Gordon Linoff
    Nov 12 at 19:04


















0














$query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"



$result = db_query($query, array(':movies' => $value));






share|improve this answer




















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268235%2fsql-query-not-returning-desired-result-with-not-like%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:



    (excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')


    E.g:



    SELECT
    '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0





    share|improve this answer






















    • Works like a charm! Thanks a lot Salman for reminding me of REGEXP
      – hyd00
      Nov 12 at 19:31















    1














    You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:



    (excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')


    E.g:



    SELECT
    '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0





    share|improve this answer






















    • Works like a charm! Thanks a lot Salman for reminding me of REGEXP
      – hyd00
      Nov 12 at 19:31













    1












    1








    1






    You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:



    (excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')


    E.g:



    SELECT
    '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0





    share|improve this answer














    You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:



    (excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')


    E.g:



    SELECT
    '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1120311' REGEXP '[[:<:]]203[[:>:]]' -- 0






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 13 at 5:57

























    answered Nov 12 at 19:11









    Salman A

    175k66336424




    175k66336424











    • Works like a charm! Thanks a lot Salman for reminding me of REGEXP
      – hyd00
      Nov 12 at 19:31
















    • Works like a charm! Thanks a lot Salman for reminding me of REGEXP
      – hyd00
      Nov 12 at 19:31















    Works like a charm! Thanks a lot Salman for reminding me of REGEXP
    – hyd00
    Nov 12 at 19:31




    Works like a charm! Thanks a lot Salman for reminding me of REGEXP
    – hyd00
    Nov 12 at 19:31













    1














    Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.



    Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.



    But, the logic would be simplified if you used find_in_set(). I think you intend:



    WHERE (`restId` = '176' OR branchId = '203' OR
    find_in_set(176, `multi_vendorId`)
    ) AND
    (find_in_set(203, `excluded_branch_id`)





    share|improve this answer






















    • Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
      – hyd00
      Nov 12 at 18:59











    • @hyd00 . .. I answered your question.
      – Gordon Linoff
      Nov 12 at 19:04















    1














    Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.



    Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.



    But, the logic would be simplified if you used find_in_set(). I think you intend:



    WHERE (`restId` = '176' OR branchId = '203' OR
    find_in_set(176, `multi_vendorId`)
    ) AND
    (find_in_set(203, `excluded_branch_id`)





    share|improve this answer






















    • Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
      – hyd00
      Nov 12 at 18:59











    • @hyd00 . .. I answered your question.
      – Gordon Linoff
      Nov 12 at 19:04













    1












    1








    1






    Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.



    Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.



    But, the logic would be simplified if you used find_in_set(). I think you intend:



    WHERE (`restId` = '176' OR branchId = '203' OR
    find_in_set(176, `multi_vendorId`)
    ) AND
    (find_in_set(203, `excluded_branch_id`)





    share|improve this answer














    Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.



    Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.



    But, the logic would be simplified if you used find_in_set(). I think you intend:



    WHERE (`restId` = '176' OR branchId = '203' OR
    find_in_set(176, `multi_vendorId`)
    ) AND
    (find_in_set(203, `excluded_branch_id`)






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 12 at 19:04

























    answered Nov 12 at 18:49









    Gordon Linoff

    757k35291399




    757k35291399











    • Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
      – hyd00
      Nov 12 at 18:59











    • @hyd00 . .. I answered your question.
      – Gordon Linoff
      Nov 12 at 19:04
















    • Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
      – hyd00
      Nov 12 at 18:59











    • @hyd00 . .. I answered your question.
      – Gordon Linoff
      Nov 12 at 19:04















    Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
    – hyd00
    Nov 12 at 18:59





    Thanks for the suggestion! So I should go after breaking it up and normalising right? Nothing can be done using the current structure to get desired result? Anyway using a subquery or any other trick?
    – hyd00
    Nov 12 at 18:59













    @hyd00 . .. I answered your question.
    – Gordon Linoff
    Nov 12 at 19:04




    @hyd00 . .. I answered your question.
    – Gordon Linoff
    Nov 12 at 19:04











    0














    $query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"



    $result = db_query($query, array(':movies' => $value));






    share|improve this answer

























      0














      $query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"



      $result = db_query($query, array(':movies' => $value));






      share|improve this answer























        0












        0








        0






        $query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"



        $result = db_query($query, array(':movies' => $value));






        share|improve this answer












        $query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"



        $result = db_query($query, array(':movies' => $value));







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 12 at 19:13









        Ammar Iqbal

        213




        213



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268235%2fsql-query-not-returning-desired-result-with-not-like%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Top Tejano songwriter Luis Silva dead of heart attack at 64

            政党

            天津地下鉄3号線