Using APEX_STRING.SPLIT in SQL

First...have I mentioned how much I don't like the blogging platform that I am using (hashnode)? Well, I don't like it. I don't like it for many reasons, but today just highlights one more reason. I'm trying to make use of the freesql embed that allows me to have a SQL editor right on the page. It's quite cool. You can read about it here. Unfortunately, after way too much time trying to get it to work today on hashnode I have given up. Instead I will just give you the code you would use if it worked. So, on to the blog post, which is hosted on hashnode...for now.

In APEX Instant Tips episode 170 Marwa and I showed how to find the matching elements of two delimited strings. A lot of people will automatically turn to PL/SQL to do this kind of thing, but it is really just an inner join in SQL. We can make use of APEX_STRING.SPLIT to do this in a single SQL statement. Try it out below:

select column_value
  from apex_string.split('a,b,c,d,e,f,g,h',',') s1
  join apex_string.split('g,h,i,j',',') s2 using (column_value)

The code to embed a SQL editor:

<iframe id="live-sql-embedded" src="https://freesql.com/embedded/?layout=vertical&compressed_code=H4sIAAAAAAAAE23NQQrCMBRF0XlX8WZp4VXQLYhjl1Bi%252FE1%252FSZPSn4jLdyQ4cH4P1yRJqAgltS1PL5%252BadMB8lA1%252Bl%252Fdk9dAcT7Ynrb3zfDDwSeHMyMXR0Q2wcwesRfM%252FErlQuX7TC5ppjuh%252Fj8M43u7XD%252FaNIwWLAAAA&&code_language=PL_SQL&code_format=false" height="460px" width="100%" frameborder="0" allowfullscreen="true" style="width:100%;border:1px solid #e0e0e0;border-radius:12px;overflow:hidden">FreeSQL Embedded Playground</iframe>

Or just click here to try it out.

Everything that does not match

select column_value
  from apex_string.split('a,b,c,d,e,f,g,h',',') s1
minus  
select column_value
  from apex_string.split('g,h,i,j',',') s2 
order by 1

The code to embed a SQL editor:

<iframe id="live-sql-embedded2" src="https://freesql.com/embedded/?layout=vertical&compressed_code=H4sIAAAAAAAAE5XMQQ7CIBAF0D2n%252BDs0%252BTWpVzCuPUJD6ZRigDYMGL29Kw%252FgO8BTSeIb%252FJ56LtPLpS4GWOue4Q55T9pqLOGiR4rtZB1nei4UrgzcLC3tGTqaHEtXwOhfXeDGyOevucLsdZGK%252BYNxGO6P2xdN70yOngAAAA%253D%253D&code_language=PL_SQL&code_format=false" height="460px" width="100%" frameborder="0" allowfullscreen="true" style="width:100%;border:1px solid #e0e0e0;border-radius:12px;overflow:hidden">FreeSQL Embedded Playground</iframe>

Or click here to try it out.

Everything that is in either list, but not both lists

select s1.column_value s1_column_value, s2.column_value s2_column_value
  from apex_string.split('a,b,c,d,e,f,g,h',',') s1
  full outer join apex_string.split('g,h,i,j',',') s2 on s1.column_value = s2.column_value
  where s1.column_value is null
     or s2.column_value is null
  order by 1, 2

The code to embed a SQL editor:

<iframe id="live-sql-embedded3" src="https://freesql.com/embedded/?layout=vertical&compressed_code=H4sIAAAAAAAAE22POw6DMBBEe04xHYm0INl9qih1joD4LGBkbOS187l9RBElgUy32vekGWHLbYSosvU2za661TYxRFXfN0H0BtA%252FQAb0wc%252BoF35UEoNxQymLNfGQ19RQSx0x9TTQmFNO%252BRGiViVZC58iB0zeuH%252F2QCMZmt6Whne7sqdtuwy4jxx4RxqBS9ZmWOPDbtXn70PHAc0TiqCL4nI9vwAl7DC2KgEAAA%253D%253D&code_language=PL_SQL&code_format=false" height="460px" width="100%" frameborder="0" allowfullscreen="true" style="width:100%;border:1px solid #e0e0e0;border-radius:12px;overflow:hidden">FreeSQL Embedded Playground</iframe>

Or click here to try it out.

Everything that is in either list, but only show once

select column_value
  from apex_string.split('a,b,c,d,e,f,g,h',',') s1
union  
select column_value
  from apex_string.split('g,h,i,j',',') s2 
order by 1

The code to embed a SQL editor:

<iframe id="live-sql-embedded5" src="https://freesql.com/embedded/?layout=vertical&compressed_code=H4sIAAAAAAAAE5XMQQ7CIBAF0D2n%252BDs0%252BTWpVzCuPUJD6ZRiKDQMGL29Kw%252FgO8BTSeIbfEl9z9PLpS4GWGvZ4Q55T9pqzOGiR4rtZB1nei4UrgzcLC3tGTqanmPJgNG%252FusCNkc9fc4UpdZGK%252BYNxGO6P2xfzRoOdngAAAA%253D%253D&code_language=PL_SQL&code_format=false" height="460px" width="100%" frameborder="0" allowfullscreen="true" style="width:100%;border:1px solid #e0e0e0;border-radius:12px;overflow:hidden">FreeSQL Embedded Playground</iframe>

Or click here to try it out.

There we have it, yet another blog post about the magic of APEX_STRING. Truly one of the great hidden features of APEX.