SQL Server

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

SQLの文法

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

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

目的

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

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

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

実現案1

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

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

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

実現案2

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

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

処理概要

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

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

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

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

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

使う関数

padindex()関数

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

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

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

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

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

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

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

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

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

substring()関数

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

replace()関数

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

 

 

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
今のスキルのままで大丈夫?

あなたのスキル。今のままで大丈夫ですか?

時代のニーズに合った開発スキルを身につけてあなたの価値を高めましょう。

\オンラインでも教室でも学べるおすすめのスクール/

TECH::CAMP(エンジニアスクール)

まずは基礎から・・・という方はProgateUdemyどっとインストールなどがおすすめ!

スキルアップについて詳しくはこちら