More than 1 year has passed since last update.

SQLServerでのTEL3分割

Posted at 2023-03-13

背景

TELが自由入力なのに、帳票に出力する際にハイフンが設定されていたら3分割して出力しないといけない。
TELを入力するのは人間なので、ハイフンが1つだったり、2つだったり、4つだったりすることもあるかもしれない。
入力項目を分けろ、という話ですが、入力項目を分けることが仕様上できない。
入力チェックで制御しろ、という話ですが、入力チェックの追加が仕様上できない。
とにかく自由入力された値をSQLで加工してあげないといけないということで、TEL分割のSQLを作成しました。

SQLServer2016以降にはSTRING_SPLIT関数があるため、それより前のバージョンでの活用となります。

実行結果

3番目にハイフンが設定されていたとしても、2番目のハイフンの後の値を取得し、TEL3とする方法

DECLARE @tel				VARCHAR(MAX) = '1234-5678-9012'
DECLARE @separator			VARCHAR(MAX) = '-'

SELECT
 @tel as TEL

,CASE
    -- 1番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel) > 0
    -- 2番目の「-」前の値を設定する
    THEN SUBSTRING(@tel,
                   1,
                   CHARINDEX(@separator, @tel) - 1)
    -- TELを全て設定する
    ELSE @tel
 END AS TEL1

,CASE
    -- 2番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) > 0
    -- 1番目の「-」後、かつ、2番目の「-」前の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel) + 1, 
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) - 1)
    -- 1番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel) > 0
    -- 1番目の「-」後の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel) + 1,
                   LEN(@tel) - CHARINDEX(@separator, @tel) + 1)
 END AS TEL2

,CASE
    -- 2番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) > 0
    -- 2番目の「-」後の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) + 1,
                   LEN(@tel) - CHARINDEX(@separator, @tel , CHARINDEX(@separator, @tel) + 1))
 END AS TEL3

3番目にハイフンが設定されていた場合、2番目のハイフンの後から3番目のハイフンの前の値を取得し、TEL3とする方法

DECLARE @tel				VARCHAR(MAX) = '123456789012'
DECLARE @separator			VARCHAR(MAX) = '-'

SELECT
 @tel as TEL

,CASE
    -- 1番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel) > 0
    -- 2番目の「-」前の値を設定する
    THEN SUBSTRING(@tel,
                   1,
                   CHARINDEX(@separator, @tel) - 1)
    -- TELを全て設定する
    ELSE @tel
 END AS TEL1

,CASE
    -- 2番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) > 0
    -- 1番目の「-」後、かつ、2番目の「-」前の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel) + 1, 
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) - 1)
    -- 1番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel) > 0
    -- 1番目の「-」後の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel) + 1,
                   LEN(@tel) - CHARINDEX(@separator, @tel) + 1)
 END AS TEL2

,CASE
    -- 2番目の「-」が設定されている、かつ、3番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) > 0 AND
         CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) + 1) > 0
    -- 2番目の「-」後、かつ、3番目の「-」前の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) + 1, 
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) + 1) - 
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - 1)
    -- 2番目の「-」が設定されている場合
    WHEN CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) - CHARINDEX(@separator, @tel) > 0
    -- 2番目の「-」後の値を設定する
    THEN SUBSTRING(@tel,
                   CHARINDEX(@separator, @tel, CHARINDEX(@separator, @tel) + 1) + 1,
                   LEN(@tel) - CHARINDEX(@separator, @tel , CHARINDEX(@separator, @tel) + 1))
 END AS TEL3

参考資料

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up