4 Replies Latest reply on Mar 9, 2012 4:41 PM by Ivy Chen

    How to locate a substring in Custom SQL

    Ivy Chen



      I have a long string in the data source, How can I use Custom SQL to find the index/location of a substring of the long string?


      ("date","product1, quantity1, product2, quantity2...") is the simplified row format in the data source, I need to extract the quantity and aggregate them group by  "date". How can I do that?


      I tried mid(longstring, locate(longstring,substring)+len(substring),locate(longstring,",",locate(longstring,substring)+len(substring))-1)

      which is not working, because locate() is not supported in Custom SQL...



        • 1. Re: How to locate a substring in Custom SQL

          Ivy, what is your data source? Different data source drivers support slightly different flavours of SQL, so exact syntax may depend on whether your data is in a text file, Oracle, SQL Server, etc.

          It also depends on the character pattern in your data, and you might have to use regular expressions to reliably pick out what you need, so posting a data sample will help.

          1 of 1 people found this helpful
          • 2. Re: How to locate a substring in Custom SQL
            Ivy Chen

            My data source is excel.


            Basically, I want to build a price column for the main product with its parts composition on one sheet and parts price on the other.

            • 3. Re: How to locate a substring in Custom SQL

              With Excel you are limited to JetSQL, which is what Tableau uses to connect to Excel files.

              To extract values from text that follows the pattern in your sample, you will need something like


                   Product.[Main Product],

                   Left([Product]![Parts],InStr([Product]![Parts],",")-1) AS Prod1,

                   Mid([Product]![Parts],InStr([Product]![Parts],",")+1,InStr([Product]![Parts],";")-InStr([Product]!Parts],",")-1) AS Quant1

              --etc. for all possible positions

              FROM Product;


              and this is only for the first two positions, the following positions will become even more complex as you go. This will most likely result in "SQL too complex" error from JetSQL driver.


              I would recommend using Excel's Data -> Text to Columns built-in funciton to convert those long strings into a set of columns in one simple step, and then use the newly structured data. You can then unpivot it (convert to what you have in your Parts sheet), if required, using Tableau's free Excel addin.


              text to columns.png

              • 4. Re: How to locate a substring in Custom SQL
                Ivy Chen

                Thanks a lot, Dimitri


                I like your first idea. I guess I will find a way to work around this issue that way.


                For the text to column function. It might work for the sample file, but as I will treat the source as a dynamic changing source, it might not be the optimal way to do that.