Tags

, , ,

How can we convert a string including seperators into it’s token using only SQL?

Easiest way is to make use of recursive query techniques and regular expression as shown below:

WITH data (string) AS (SELECT 'MEIER,HUBER,MUELLER,KUHN,,KOLLER' FROM dual)
SELECT REGEXP_SUBSTR (data.string, '[^,]+', 1, ROWNUM) element_string  
  FROM data 
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (DATA.STRING, '[^,]+'))  + 1
/

ELEMENT_STRING
--------------
MEIER
HUBER
MUELLER
KUHN
KOLLER
(null)