7 Replies Latest reply: Sep 10, 2013 1:27 PM by Frank Kulash RSS

    CONCAT not working as documented

    59cc077b-a90a-417c-b5db-03aacdd6baac

      I've written a SP and it refuses to work with the CONCAT function as documented.

       

      >>Yes I have searched the web and other locales to no avail.

       

      Create Procedure `SP_CODE`(IN search_term VarChar(200))

      begin

        if search_term is not null then

          begin

             Select b.s_term, a.O_Number, a.S_CODE, a.LONG_DESC

             From JKC a INNER JOIN search_terms b ON a.S_CODE = b.S_CODE

             Where b.s_term like CONCAT('%', search_term, '%');

          end;

      end;

       

       

      The editor believes that the problem is at CONCAT('%', search_term, '%');

       

      Any ideas what might be the problem would be helpful. THX...

        • 1. Re: CONCAT not working as documented
          Frank Kulash

          Hi,

           

          The CONCAT function takes exactly 2 arguments.  The version 11.2 SQL Language manual:

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions033.htm#sthref1005

          is clear about that.  What is the documentation you saw, where it said the function could take 3 arguments?

           

          A lot of guys always use the || operator instead of CONCAT:

           

          Where b.s_term like '%' || search_term || '%'

           

          If you really need to use CONCAT, and you have more than 2 items, then nest multiple calls to CONCAT.  For example:

           

          Where  b.s_term like  CONCAT ( '%'
                                       , CONCAT ( search_term, '%')
                                       )

          or

           

          Where  b.s_term like  CONCAT ( CONCAT ('%', search_term)
                                       , '%'
                                       )

          If you have N items to concatenate, then (if you're using CONCAT) you need to call it N-1 times, because each call reduces the number of items by 1.

          • 2. Re: CONCAT not working as documented
            Brian Bontrager

            According to Oracle's documentation, the CONCAT() PL/SQL function takes 2 parameters.

            You might really want the concatenation operator

             

            Where b.s_term like '%'||search_term||'%';



            Oracle's CONCAT function  is not the same as SQL Server's, which is documented to work the way you are using it.

            CONCAT (Transact-SQL)

            • 3. Re: CONCAT not working as documented
              JustinCave

              Posting the actual Oracle error message is always helpful.

               

              It appears, though, that there are a couple of issues.

               

              First off, CONCAT only takes two parameters.  You could, I suppose, use two nested CONCAT statements, i.e.

               

              CONCAT( '%', CONCAT( search_term, '%' ))
              
              

               

              It would be far more natural, however, to use the concatenation operator ||

               

              '%' || search_term || '%'
              
              

               

              Second, your SELECT statement isn't doing anything with the results.  If you have a SELECT statement in a stored procedure in Oracle, you have to do something with the results.  If you are always going to fetch exactly one row (which looks unlikely), you could do a SELECT INTO some local variables.  Otherwise, you'd need to do something like iterate over the results of the SELECT statement in a LOOP or open a SYS_REFCURSOR that is an OUT parameter to the procedure in order to return the results to the caller (who would then have to write code to iterate over the results).

               

              I'm assuming also that you understand the performance implications of putting the leading wildcard in your LIKE...  Are you sure you wouldn't be better served using Oracle Text and a CONTAINS predicate?

               

              Actually, on reflection, you're also missing the END IF. 

               

              if search_term is not null 
              then
                <<your SELECT statement>>
              end if;
              

               

              The BEGIN and END after your THEN clause are not doing anything for you-- you can eliminate them without changing the functionality.  Or you can keep them and add in the extra END IF.

               

              Justin   

              • 4. Re: CONCAT not working as documented
                SomeoneElse

                As documented, CONCAT takes only 2 arguments.  But you have 3 in your example.

                 

                Just use the concatenation operator.

                 

                where b.s_term like '%' || search_term || '%'


                • 5. Re: CONCAT not working as documented
                  59cc077b-a90a-417c-b5db-03aacdd6baac

                  Thanks for the pointers. Yes I do expect back multi-row results sets. I will look into those issues after I get this working.

                   

                  Yes I did read about the performance implications for leading wild card. I will look into Oracle Text and a Contains predicate if I decide that the leading is actually needed.

                   

                  So I have changed it to use both nested CONCAT and also tried it with the concatenation operator || as well. They work fine in a straight SQL statement. However when I try them in the SP I get the same results. I have tried this

                   

                  where b.s_term like '%'||search_term||'%'; and the editor balks at the last '%'

                   

                  so I tried remove the leading wildcard so it says CONCAT(search_term, '%'); and still it balks at the );


                  here is the error;

                   

                  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

                  • 6. Re: CONCAT not working as documented
                    Pablolee

                    can i ask you why you are asking a question about mysql on an oracle forum?

                    • 7. Re: CONCAT not working as documented
                      Frank Kulash

                      Hi,

                       

                       

                      59cc077b-a90a-417c-b5db-03aacdd6baac wrote:

                       

                      Thanks for the pointers. Yes I do expect back multi-row results sets. I will look into those issues after I get this working.

                       

                      Yes I did read about the performance implications for leading wild card. I will look into Oracle Text and a Contains predicate if I decide that the leading is actually needed.

                       

                      So I have changed it to use both nested CONCAT and also tried it with the concatenation operator || as well. They work fine in a straight SQL statement. However when I try them in the SP I get the same results. I have tried this

                       

                      where b.s_term like '%'||search_term||'%'; and the editor balks at the last '%'

                       

                      so I tried remove the leading wildcard so it says CONCAT(search_term, '%'); and still it balks at the );


                      here is the error;

                       

                      Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

                      Sometimes, the compiler can't pinpoint exactly where the error occurs in a statement, so it flags an arbitrary point (like the last character) in the offending statement.  For example, there are different ways of saying what you want to do with the results of a SELECT.  One might require the keyword INTO a a certain sport, another might require a different keyword at a different spot.  If you don't speicify any way of catching the results, how can it say exactly what was missing, or where the error occurred?

                      Always post a complete script that people can run to re-create the problem and test their ideas.

                       

                      This is an Oracle forum.  If you have a MySQL problem, maybe a MySQL forum would be a better place to post it.  Try:

                      http://forums.mysql.com