3 Replies Latest reply on Oct 19, 2012 3:58 AM by Russell Christopher

    SQL Instring

    Alan Kwan

      Hi all,

       

      Trying to use the INSTR function on Tableau SQL, but I keep getting the following error message:

       

      Database error 0x80040E14: Invalid procedure call

       

      My sql is as follows:

       

      SELECT

        LEFT("Request ID",Instr(1,"Request ID")-1) AS "BU"

      FROM [TSR]

       

      Thanks,

      Alan

        • 1. Re: SQL Instring
          Russell Christopher

          Hi Alan!

           

          What database are you connecting to? Most of the time you'll want to be using single quotes instead of double quotes, unless you're connecting to something like Access.

           

          Does the same SQL work when you execute it directly against the database in question outside of Tableau?

          • 2. Re: SQL Instring
            Alan Kwan

            Hi I am connecting it to Access. It's funny how the LEFT and INSTR function works on Access, but doesn't work on Toad (another SQL tool I use).

             

            Out of curiosity, do different tools have different set of "SQL functions"?

            • 3. Re: SQL Instring
              Russell Christopher

              While syntax can be different for each database's implementation of instr(), your syntax is not right -- at least for Access.

               

              Your instr is only using two arguments:

               

              Instr(1,"Request ID")

               

              You're essentially beginning to ask the question: "Starting with the first character in the string Request ID....", but you don't give Instr a 3rd argument which is telling it what to look for. Your expression shouldn't work in Access at all.

               

              http://office.microsoft.com/en-us/access-help/instr-function-HA001228857.aspx

               

              You want something like instr(1,[FieldName], "string to search for inside [FieldName]"), I think.

               

              I'm not sure I understand your last question. Can you re-phrase?