SQLServerで文字列の中から連続する数字を取得する

SQL Serverに格納された文字列の中から数字のみ取得してほしいというお題があり、なんとか対応しましたので記録しておきます。

もう一回やるとき思い出すのが大変なので…。

目的

備考のようなテキスト文字列に商品コードが含まれている場合に、その商品コードのみ取得したい…。という依頼です。

そもそものテーブル設計がいけていないのですが昔からあるシステムなので私の関知しないところです。ご了承ください…。

※前提として商品コードは数字のみ5ケタ固定とします。

実現案１

TRANSLATE関数を使うと比較的簡単に数字のみを取得できるようですが、残念ながらTRANSLATE関数はSQL Server2017以降に実装されたようで、私の環境では利用できませんでした。

参考URL：文字列から数値のみを抽出する – Qiita

※もちろん試していません。SQL Server2017以降の環境の方は上記URLを参考にされてください。

実現案２

先にサンプルコードを提示します。

SELECT 
CASE WHEN patindex('%[01][0-9][0-9][0-9][0-9]%', 備考)=0 THEN '' 
          ELSE 
           substring(備考,patindex('%[01][0-9][0-9][0-9][0-9]%', 備考),5) END AS 商品コード1,
CASE WHEN patindex('%[01][0-9][0-9][0-9][0-9]%', 
             replace(備考, substring(備考,patindex('%[01][0-9][0-9][0-9][0-9]%', 備考),5), ''))=0 THEN '' 
          ELSE 
           substring(replace(備考, substring(備考,patindex('%[01][0-9][0-9][0-9][0-9]%', 備考),5), ''),
             patindex('%[01][0-9][0-9][0-9][0-9]%', 
             replace(備考, substring(備考,patindex('%[01][0-9][0-9][0-9][0-9]%', 備考),5), '')),5) END AS 商品コード2
FROM 商品伝票;

とても分かりにくいです💦

処理概要

①文字列から連続する数字（正規表現で取得）が出現する位置を取得。

②①で取得した位置から5文字を取得　➡　商品コード1

③1つ目の商品コードをNULL置換で除外した文字列から、連続する数字が出現する位置を取得。

④1つ目の商品コードを除外した文字列の、③で取得した位置から5文字を取得　➡　商品コード2

※ただし、位置を取得した際、戻り値が０の場合は空文字を返す。

使う関数

padindex()関数

構文：PADINDEX( パターン文字列, 検索対象文字列)

検索対象文字列データからパターンに一致する文字列を検索します。

戻り値は検索対象文字列の中にパターン文字列が含まれる場合、最初に一致した文字列開始位置です。

※パターン文字列が見つからなかった場合は戻り値として０が返されます。

※パターン文字列は正規表現を使います。LIKEと同様の使い方です。

連続する5つの数字の表現は[0-9][0-9][0-9][0-9][0-9]と表現できます。

連続する５つの数字が文字列の中に含まれる前提ですので前後に％をつけて表現します。

padindex(‘%[0-9][0-9][0-9][0-9][0-9]%’, 備考)

上記の例の場合、padindex(‘%[0-9][0-9][0-9][0-9][0-9]%’, 備考)での戻り値は５です。

substring()関数

構文：SUBSTRING( 検索対象文字列, 開始位置, 長さ)

replace()関数

構文：REPLACE( 対象文字列, 検索文字列, 置換文字列)