LIKE doesn't return any record when used with SUBSTRSQL select join: is it possible to prefix all columns as 'prefix.*'?How can a LEFT OUTER JOIN return more records than exist in the left table?MySQL join with where clauseMySQL - UPDATE query based on SELECT QuerySQL Server: How to Join to first rowWhy query with MAX(Date) dosen't return any record?Return “null” value if no record for dateSQL query doesn't return enough recordsDuplicated field when using aliasSub-Query doesn't return expected result

Is there a list of world wide upcoming space events on the web?

"I will not" or "I don't" as an answer for negative orders?

Why would an airline put 15 passengers at once on standby?

Discrepancy regarding AoE point of origin between English and German PHB

LM324 - Issue with output in negative feedback

Smallest PRIME containing the first 11 primes as sub-strings

Where to find the Arxiv endorsement code?

Question about a degree 5 polynomial with no rational roots

My machine, client installed VPN,

How do we know neutrons have no charge?

Concerning a relationship in the team

Are the coefficients of certain product of Rogers-Ramanujan Continued Fraction non-negative?

Why do some modern glider wings like the Schleicher 29 have a tadpole shape rather than a teardrop shape?

Is population size a parameter, or sample size a statistic?

Why does my browser attempt to download pages from http://clhs.lisp.se instead of viewing them normally?

Selection Sort Algorithm (Python)

How do my husband and I get over our fear of having another difficult baby?

What happens to a net with the Returning Weapon artificer infusion after it hits?

Speed and Velocity in Russian

Why is STARTTLS still used?

How to prevent pickpocketing in busy bars?

Is determiner 'a' needed here?

Maximize assigned tasks to each worker

Top off gas with old oil, is that bad?



LIKE doesn't return any record when used with SUBSTR


SQL select join: is it possible to prefix all columns as 'prefix.*'?How can a LEFT OUTER JOIN return more records than exist in the left table?MySQL join with where clauseMySQL - UPDATE query based on SELECT QuerySQL Server: How to Join to first rowWhy query with MAX(Date) dosen't return any record?Return “null” value if no record for dateSQL query doesn't return enough recordsDuplicated field when using aliasSub-Query doesn't return expected result






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








0















I have two tables:



coach_career



coach_id | start | end 
483368 2017-01-01 NULL


competition_seasons



coach_id | name 
483368 2017/2018


I have wrote query:



 SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368


Essentially I need to return the coach which have as start value the year of name field available in competition_season, I used SUBSTR to extract only the year, the problem's that this query return no records.










share|improve this question
























  • Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

    – mtr.web
    Mar 28 at 19:25












  • If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

    – sfarzoso
    Mar 28 at 19:26






  • 2





    This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

    – JNevill
    Mar 28 at 19:27












  • @JNevill You're right, I completely overlooked it, thanks..

    – sfarzoso
    Mar 28 at 19:28











  • Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

    – JNevill
    Mar 28 at 19:29


















0















I have two tables:



coach_career



coach_id | start | end 
483368 2017-01-01 NULL


competition_seasons



coach_id | name 
483368 2017/2018


I have wrote query:



 SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368


Essentially I need to return the coach which have as start value the year of name field available in competition_season, I used SUBSTR to extract only the year, the problem's that this query return no records.










share|improve this question
























  • Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

    – mtr.web
    Mar 28 at 19:25












  • If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

    – sfarzoso
    Mar 28 at 19:26






  • 2





    This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

    – JNevill
    Mar 28 at 19:27












  • @JNevill You're right, I completely overlooked it, thanks..

    – sfarzoso
    Mar 28 at 19:28











  • Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

    – JNevill
    Mar 28 at 19:29














0












0








0








I have two tables:



coach_career



coach_id | start | end 
483368 2017-01-01 NULL


competition_seasons



coach_id | name 
483368 2017/2018


I have wrote query:



 SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368


Essentially I need to return the coach which have as start value the year of name field available in competition_season, I used SUBSTR to extract only the year, the problem's that this query return no records.










share|improve this question














I have two tables:



coach_career



coach_id | start | end 
483368 2017-01-01 NULL


competition_seasons



coach_id | name 
483368 2017/2018


I have wrote query:



 SELECT * FROM coach_career cr
INNER JOIN competition_seasons s
ON SUBSTR(cr.start, 1, 4) = s.name
WHERE cr.id = 483368


Essentially I need to return the coach which have as start value the year of name field available in competition_season, I used SUBSTR to extract only the year, the problem's that this query return no records.







mysql sql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Mar 28 at 19:22









sfarzososfarzoso

921 silver badge14 bronze badges




921 silver badge14 bronze badges















  • Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

    – mtr.web
    Mar 28 at 19:25












  • If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

    – sfarzoso
    Mar 28 at 19:26






  • 2





    This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

    – JNevill
    Mar 28 at 19:27












  • @JNevill You're right, I completely overlooked it, thanks..

    – sfarzoso
    Mar 28 at 19:28











  • Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

    – JNevill
    Mar 28 at 19:29


















  • Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

    – mtr.web
    Mar 28 at 19:25












  • If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

    – sfarzoso
    Mar 28 at 19:26






  • 2





    This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

    – JNevill
    Mar 28 at 19:27












  • @JNevill You're right, I completely overlooked it, thanks..

    – sfarzoso
    Mar 28 at 19:28











  • Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

    – JNevill
    Mar 28 at 19:29

















Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

– mtr.web
Mar 28 at 19:25






Have you tried SELECT SUBSTR(cr.start, 1, 4) FROM coach_career and SELECT name FROM competition_seasons to see if there are any matching values?

– mtr.web
Mar 28 at 19:25














If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

– sfarzoso
Mar 28 at 19:26





If I remove the SUBSTR the record is returned correctly, should be a mistake on SUBSTR

– sfarzoso
Mar 28 at 19:26




2




2





This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

– JNevill
Mar 28 at 19:27






This is a date, so don't substring on it. Instead extract the year using ON Year(cr.Start) = s.name Unless you're storing your dates as varchar. In which case... YUCK.

– JNevill
Mar 28 at 19:27














@JNevill You're right, I completely overlooked it, thanks..

– sfarzoso
Mar 28 at 19:28





@JNevill You're right, I completely overlooked it, thanks..

– sfarzoso
Mar 28 at 19:28













Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

– JNevill
Mar 28 at 19:29






Also.. your name value is 2017/2018. You have two values in a single column. You are going to have a bad time here. Perhaps something like ON s.name LIKE CONCAT('%', YEAR(cr.start), '%') that's not going to be quick though. Ultimately you'd be better off storing two records in this table (one for each year/name).

– JNevill
Mar 28 at 19:29













2 Answers
2






active

oldest

votes


















1
















This:



SELECT * FROM coach_career cr 
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
WHERE cr.id = 483368


will join the tables when name contains the value of start.

If start's data type is date then use date_format() to extract the year like this:



SELECT * FROM coach_career cr 
INNER JOIN competition_seasons s
ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
WHERE cr.id = 483368


Are you sure about using the condition:



WHERE cr.id = 483368


It does not seem right.






share|improve this answer


































    1
















    if both are string values,



    SUBSTR(cr.start, 1, 4) is equal to "2017".



    s.name = "2017/2018".



    The 2 values are NOT equal.






    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/4.0/"u003ecc by-sa 4.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%2f55405414%2flike-doesnt-return-any-record-when-used-with-substr%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1
















      This:



      SELECT * FROM coach_career cr 
      INNER JOIN competition_seasons s
      ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
      WHERE cr.id = 483368


      will join the tables when name contains the value of start.

      If start's data type is date then use date_format() to extract the year like this:



      SELECT * FROM coach_career cr 
      INNER JOIN competition_seasons s
      ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
      WHERE cr.id = 483368


      Are you sure about using the condition:



      WHERE cr.id = 483368


      It does not seem right.






      share|improve this answer































        1
















        This:



        SELECT * FROM coach_career cr 
        INNER JOIN competition_seasons s
        ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
        WHERE cr.id = 483368


        will join the tables when name contains the value of start.

        If start's data type is date then use date_format() to extract the year like this:



        SELECT * FROM coach_career cr 
        INNER JOIN competition_seasons s
        ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
        WHERE cr.id = 483368


        Are you sure about using the condition:



        WHERE cr.id = 483368


        It does not seem right.






        share|improve this answer





























          1














          1










          1









          This:



          SELECT * FROM coach_career cr 
          INNER JOIN competition_seasons s
          ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
          WHERE cr.id = 483368


          will join the tables when name contains the value of start.

          If start's data type is date then use date_format() to extract the year like this:



          SELECT * FROM coach_career cr 
          INNER JOIN competition_seasons s
          ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
          WHERE cr.id = 483368


          Are you sure about using the condition:



          WHERE cr.id = 483368


          It does not seem right.






          share|improve this answer















          This:



          SELECT * FROM coach_career cr 
          INNER JOIN competition_seasons s
          ON s.name LIKE concat('%', SUBSTR(cr.start, 1, 4), '%')
          WHERE cr.id = 483368


          will join the tables when name contains the value of start.

          If start's data type is date then use date_format() to extract the year like this:



          SELECT * FROM coach_career cr 
          INNER JOIN competition_seasons s
          ON s.name LIKE concat('%', date_format(cr.start, '%Y'), '%')
          WHERE cr.id = 483368


          Are you sure about using the condition:



          WHERE cr.id = 483368


          It does not seem right.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Mar 28 at 19:45

























          answered Mar 28 at 19:33









          forpasforpas

          45.1k6 gold badges13 silver badges34 bronze badges




          45.1k6 gold badges13 silver badges34 bronze badges


























              1
















              if both are string values,



              SUBSTR(cr.start, 1, 4) is equal to "2017".



              s.name = "2017/2018".



              The 2 values are NOT equal.






              share|improve this answer





























                1
















                if both are string values,



                SUBSTR(cr.start, 1, 4) is equal to "2017".



                s.name = "2017/2018".



                The 2 values are NOT equal.






                share|improve this answer



























                  1














                  1










                  1









                  if both are string values,



                  SUBSTR(cr.start, 1, 4) is equal to "2017".



                  s.name = "2017/2018".



                  The 2 values are NOT equal.






                  share|improve this answer













                  if both are string values,



                  SUBSTR(cr.start, 1, 4) is equal to "2017".



                  s.name = "2017/2018".



                  The 2 values are NOT equal.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Mar 28 at 19:28









                  BWSBWS

                  3,52213 silver badges23 bronze badges




                  3,52213 silver badges23 bronze badges































                      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.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55405414%2flike-doesnt-return-any-record-when-used-with-substr%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

                      Kamusi Yaliyomo Aina za kamusi | Muundo wa kamusi | Faida za kamusi | Dhima ya picha katika kamusi | Marejeo | Tazama pia | Viungo vya nje | UrambazajiKuhusu kamusiGo-SwahiliWiki-KamusiKamusi ya Kiswahili na Kiingerezakuihariri na kuongeza habari

                      SQL error code 1064 with creating Laravel foreign keysForeign key constraints: When to use ON UPDATE and ON DELETEDropping column with foreign key Laravel error: General error: 1025 Error on renameLaravel SQL Can't create tableLaravel Migration foreign key errorLaravel php artisan migrate:refresh giving a syntax errorSQLSTATE[42S01]: Base table or view already exists or Base table or view already exists: 1050 Tableerror in migrating laravel file to xampp serverSyntax error or access violation: 1064:syntax to use near 'unsigned not null, modelName varchar(191) not null, title varchar(191) not nLaravel cannot create new table field in mysqlLaravel 5.7:Last migration creates table but is not registered in the migration table

                      은진 송씨 목차 역사 본관 분파 인물 조선 왕실과의 인척 관계 집성촌 항렬자 인구 같이 보기 각주 둘러보기 메뉴은진 송씨세종실록 149권, 지리지 충청도 공주목 은진현