• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: Regular Expression

Column to rows

23 Thursday May 2013

Posted by troroblog in ORACLE, Regular Expression, SQL, Tipps and Tricks

≈ Leave a comment

Tags

CONNECT BY, ORACLE, Regular Expression, SQL

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)

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...