【SQL Server】TRANSLATE的な関数を作ってみる

SQL Server 2017からTRANSLATE関数が導入されたことはご存じだろうか？
Oracleなんかには以前よりあったから、よほど要望が強かったんでしょうね。
で、実際にTRANSLATE関数を使いたいけどSQL Server 2016以前が本番環境で動いていることもまだまだたくさんあることでしょう。
そんな方は以下をご覧ください。解決します。

4-1 OracleのTranslate関数を模倣
https://oraclesqlpuzzle.ninja-web.net/sqlserver/sqlserver-4-1.html

ただ、私がやりたかったのは、ビットが立っているところを特定の文字列に変えたかったのです。
例えば、
「0101101」　--> 「　火　木金　日」

これは、先ほどの「OracleのTranslate関数を模倣」に書かれているコードをちょろっと変えるだけで実現できます。
何気にこのような変換する機会はあると思いますので、覚えておくと役に立つかもしれません。

CREATE FUNCTION dbo.Bit列To曜日
(
	@曜日Bit列		char(7)
)
returns nvarchar(7)
as
begin
    declare @返却値		nvarchar(7) = '';
    declare @i			int = 1;
    declare @HitPos		int;

    declare @曜日リスト nchar(7);

    set @曜日リスト = '月火水木金土日';

    if @曜日Bit列 is null
		return null;

    while @i <= len(@曜日Bit列)
    begin
        set @HitPos = CharIndex(SubString(@曜日Bit列, @i, 1),'1111111', @i)

        if @HitPos = 0
            set @返却値 = @返却値 + '　'
        else
            set @返却値 = @返却値 + SubString(@曜日リスト, @HitPos, 1)

        set @i = @i + 1
    end;

    return @返却値;

end

ちょっと注意点を書いておくと、
declare @返却値 nvarchar(7) = '';
と、nvarcharで宣言しているのがポイントです。これをncharと固定長で書いてしまうと
set @返却値 = @返却値 + '　'
といったところで問題が発生します。もし、@返却値がnchar(7)の固定長だとすると、上記のsetの結果は、@返却値に空白が8文字分入っている状態になります。
実はT-SQLでストアドプロシージャやストアドファンクションを書く際には型の宣言が非常に重要で、これを誤っていると想定しない結果が返ってくることになります。
数値型でもそうです。思わぬバグにつながりますので注意しましょう。

select 5 / 2;

＃結果は、2

select 5 / CONVERT(decimal, 2);

＃結果は、2.500000