SQL Serve で文字列から数字だけを取り出す

SQL Serverでデータから数字だけを取り出したい事案があったので忘れないようにメモ。具体的には西濃のカンガルーマジックにデータをアップしたら

番号に半角数字以外の文字が含まれています

と怒られたのでその対策。電話番号が「(123)456-7890」みたいになってたんですね。ハイフンはOKだけどカッコがダメ。

ググったらTRANSLATE関数を使った例があったけど、
文字列から数値のみを抽出する - Qiita

TRANSLATE関数はSQL Server 2017から対応した関数のようで、今回の案件は2012なので使えない。ためしに2017で実行してみたら

TRANSLATE 組み込み関数の 2 番目と 3 番目の引数の文字数は、同じである必要があります。

となってどっちにしても目的の動作しないみたい。

まあ単なる文字列操作なのでいろいろ方法はあるだろうけど、REPLACE関数とPATINDEX関数を使った方法がシンプルで良さそう。

sql-server - SQL置換関数内の正規表現パターン?regex | CODE Q&A [日本語]

PATINDEX関数は

すべて有効なテキスト データ型と文字データ型で指定された式の中で、パターンが最初に現れる先頭位置を返します。パターンが見つからない場合は、0 を返します。
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/patindex-transact-sql

さらにワイルドカードもつかえるので、たとえば

PatIndex('%[^0-9]%', '(123)456-7890')

とすると、'(123)456-7890' という文字列の中で最初に見つかった数字以外の文字の位置をかえすので、ぐるぐるまわしながらひとつずつ見つけた文字を置き換えていけばよい。

CREATE FUNCTION dbo.Sujidakeyo
(
    @param varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
    WHILE PatIndex('%[^0-9]%', @param) > 0
        SELECT  @param = Replace(@param, Substring(@param, PatIndex('%[^0-9]%', @param), 1), '')

    RETURN @param 

END
GO

SELECT dbo.Sujidakeyo('(123)456-7890')