Splitっぽい処理をSQLで実現する

普通のプログラミング言語だと簡単に実装できるのに、SQLだとなんか作りにくいぞ？というこんな問題がありました。

特定の文字(今回はドット".")で、一つの文字列を3つのカラムに分割する
ただし、文字列に含まれるドットの数は決まっていない。(0個〜無制限)
ドットの数が3つ以上ある場合は3つ目のカラムにドットが残る
使用するRDBMSはSQL Server
分割するイメージは以下の通り
- ab → ab 空白空白
- ab.cde → ab cde 空白
- ab.cde.fghi → ab cde fghi
- ab.cde.fghi.j → ab cde fghi.j
- ab.cde.fghi.j.k → ab cde fghi.j.k

普通のSQLで一発で取得しようとすると、CHARINDEX関数やSUBSTRING関数の雨アラレになってしまい、非常に読みにくくなるので共通テーブル式(CTE)を使って少しメンテナンス性を高めてみました。
処理的にはCTEをパイプっぽく連鎖させているイメージです。

これがエレガントか？と言われるとちょっと苦しいですが、わかりやすいエイリアスが自由に付けられるのと、重複する計算式を排除できるのがまだマシかなと思います。

DECLARE @delimiter CHAR(1)
SELECT @delimiter = '.'

CREATE TABLE #temp (target VARCHAR(20))

INSERT INTO #temp VALUES ('')                
INSERT INTO #temp VALUES ('ab')             
INSERT INTO #temp VALUES ('ab.cde')         
INSERT INTO #temp VALUES ('ab.cde.fghi')  
INSERT INTO #temp VALUES ('ab.cde.fghi.j') 
INSERT INTO #temp VALUES ('ab.cde.fghi.j.k');
                         
WITH first_pos_data AS (
    SELECT
        target
        , CHARINDEX(@delimiter, target) AS [first_pos] 
    FROM
        #temp
),
second_pos_data AS (
    SELECT
        *
        , CASE 
            WHEN first_pos = 0 
            THEN 0 
            ELSE CHARINDEX(@delimiter, target, first_pos + 1) 
          END AS [second_pos] 
    FROM
        first_pos_data
),
length_and_start_pos_data AS (
    SELECT
        *
        , first_pos - 1 AS [left_length]
        , first_pos + 1 AS [middle_start_pos]
        , CASE 
            WHEN second_pos = 0 
            THEN LEN(target) 
            ELSE second_pos - first_pos - 1 
          END AS middle_length
        , second_pos + 1 AS [right_start_pos] 
    FROM
        second_pos_data
)
SELECT
    target
	, first_pos AS [1_pos]
	, second_pos AS [2_pos]
	, left_length AS [l_len]
	, middle_start_pos AS [m_start]
	, middle_length AS [m_len]
	, right_start_pos AS [r_start]
    , CASE 
        WHEN first_pos = 0 
        THEN target 
        ELSE LEFT(target, left_length) 
      END AS [l_val]
    , CASE 
        WHEN first_pos = 0 
        THEN '' 
        ELSE SUBSTRING(target, middle_start_pos, middle_length) 
      END AS [m_val]
    , CASE 
        WHEN second_pos = 0 
        THEN '' 
        ELSE SUBSTRING(target, right_start_pos, LEN(target)) 
      END AS [r_val] 
FROM
    length_and_start_pos_data

DROP TABLE #temp