title: Create dynamic string for IN clause
body: <p>We are using Matillion ETL API to pass query parameters to the underlying Redshift query.
  A query parameter variable for <code>States</code> is created using concatenation from the dropdown list and passed in the API as follows:</p>
  <pre><code>v_state=&quot; &amp; #encodeForURL(&quot;''AL'',''CA''&quot;)#
  </code></pre>
  <p>This variable is expected to be passed as <code>and state in ('AL','CA')</code> in the query but due to the presence of <code>,</code> it throws an error for using reserved character. I have tried escaping this character but it still doesn't work.</p>
  <p>So instead of <code>,</code> I used <code>|</code> in the variable <code>v_state=&quot; &amp; #encodeForURL(&quot;''AL''|''CA''&quot;)# </code> and it does run the API but the underlying query doesn't return any data.</p>
  <p><code>and state in ('AL','CA')</code>  (Returns data)</p>
  <p><code>and state in (replace('''AL''|''CA''','|',','))</code> (Doesn’t return data)</p>
  <p>The questions would be:</p>
  <ol>
  <li>In the Matillion API, is there a way to escape comma in the string?</li>
  <li>What's the correct way to use pipe delimited string for the IN clause in Redshift?</li>
  </ol>
  <p>Edit 1: Found a way to convert state string into rows.</p>
  <pre><code>    with t as
          (select replace('AL|CO|CA|MN', '|', ',') as state)
     , seq_0_9 as (
      select 0 as num
      union all
      select 1 as num
      union all
      select 2 as num
      union all
      select 3 as num
      union all
      select 4 as num
      union all
      select 5 as num
      union all
      select 6 as num
      union all
      select 7 as num
      union all
      select 8 as num
      union all
      select 9 as num
  )
     , seq_0_99 as (
      select a.num + b.num * 10 as num
      from seq_0_9 a,
           seq_0_9 b
  )
  SELECT split_part(t.state, ',', num) AS state
  FROM t
           JOIN seq_0_99 seq
                ON num &lt;= regexp_count(t.state, ',') + 1
  WHERE num &gt; 0;
  </code></pre>