読者です 読者をやめる 読者になる 読者になる

give IT a try

プログラミング、リモートワーク、田舎暮らし、音楽、etc.

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

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

  • 特定の文字(今回はドット".")で、一つの文字列を3つのカラムに分割する
  • ただし、文字列に含まれるドットの数は決まっていない。(0個〜無制限)
  • ドットの数が3つ以上ある場合は3つ目のカラムにドットが残る
  • 使用するRDBMSSQL 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


実行結果

target 1_pos 2_pos l_len m_start m_len r_start l_val m_val r_val
0 0 -1 1 0 1
ab 0 0 -1 1 2 1 ab
ab.cde 3 0 2 4 6 1 ab cde
ab.cde.fghi 3 7 2 4 3 8 ab cde fghi
ab.cde.fghi.j 3 7 2 4 3 8 ab cde fghi.j
ab.cde.fghi.j.k 3 7 2 4 3 8 ab cde fghi.j.k


SQLって基本的にめちゃくちゃパワフルだと思いますが、普通のプログラミング言語でいうところの配列みたいなデータを扱おうとすると結構苦労しますよね。
たとえばストアドのパラメータにIN句用の値を複数渡したいときとか。


もっとエレガントに書く方法をご存知の方はぜひ教えてください〜。