以下のカラムを持つテーブルがある。

tel_shigai VARCHAR(6), -- 電話番号 市外局番
tel_shinai VARCHAR(6), -- 電話番号 市内局番
tel_bango VARCHAR(4), -- 電話番号

これを 出力するときは - で連結せよ、という仕様があった。

今までは単純に

tel_shigai + '-' + tel_shinai + '-' + tel_bango AS tel_no

としていたが、これだとどれかが NULL か 空文字でもハイフンがくっついてしまう。
全部空の場合は -- という出力になってしまう。

ということでユーザ定義関数を作成した。
郵便番号を連結する場合は、第三引数を null として渡す必要がある。
引数を可変長の配列で受け取ったりできればループを使って簡潔に書けそうだが、
どうも SQL Server2000 ではできないようなので if 文を連ねてみた。

-- 郵便番号、電話番号、FAX 番号を - で連結する
GO
DROP FUNCTION cat_number;
GO
CREATE FUNCTION cat_number(@arg1 VARCHAR(8), @arg2 VARCHAR(8), @arg3 VARCHAR(8))
RETURNS VARCHAR(64)
AS BEGIN

  DECLARE
    @str VARCHAR(64)
  IF (@arg1 IS NOT NULL) AND (@arg1 <> '') AND (@arg2 IS NOT NULL) AND (@arg2 <> '') BEGIN
    SET @str = @arg1 + '-' + @arg2
  END ELSE BEGIN
    SET @str = COALESCE(@arg1, '') +  COALESCE(@arg2, '')
  END

  IF (@str IS NOT NULL) AND (@str <> '') AND (@arg3 IS NOT NULL) AND (@arg3 <> '') BEGIN
    SET @str = @str + '-' + @arg3
  END ELSE BEGIN
    SET @str =  COALESCE(@str, '')  +  COALESCE(@arg3, '')
  END

  RETURN @str
END

CONCAT_NULL_YIELDS_NULL をオフにしておかないと、
NULL と文字列連結した結果は必ず NULL になってしまう。

SET CONCAT_NULL_YIELDS_NULL
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_se ...

http://www.1101.com/essay/

以前読んだ
Ｌｅｓｓｏｎ１２６ 考える方法をならったことがありますか？（２）
http://www.1101.com/essay/2002-12-11.html
は非常に参考になった。
自分の頭で考えるとはどういうことなのかを噛み砕いて書いてある。