Microsoft SQL Server カスタム関数コレクション

SQL Serverには、開発者が日常業務で頻繁に使用する多くの処理をカスタム関数として実装できる柔軟性があります。ここでは、実用的なカスタム関数をコレクションとしてご紹介します。これらの関数は文字列操作、日時処理、データ変換など多岐にわたり、データベース開発の効率化に役立ちます。

文字列処理関数

文字列操作はデータ処理の基本です。以下に代表的な文字列処理関数をいくつか紹介します。

HTMLタグ除去関数

HTMLタグとその内容を文字列から除去する関数です。
CREATE FUNCTION [dbo].[RemoveHtmlTags] (@inputText NVARCHAR(8000))
RETURNS NVARCHAR(8000)
AS
BEGIN
    DECLARE @i INT
    WHILE 1 = 1
    BEGIN
        SET @i = LEN(@inputText)
        SET @inputText = REPLACE(
            @inputText, 
            SUBSTRING(@inputText, CHARINDEX('<', @inputText), 
            CHARINDEX('>', @inputText) - CHARINDEX('<', @inputText) + 1), 
            SPACE(0)
        )
        IF @i = LEN(@inputText)
            BREAK
    END

    SET @inputText = REPLACE(@inputText, ' ', '')
    SET @inputText = REPLACE(@inputText, ' ', '')
    SET @inputText = LTRIM(RTRIM(@inputText))
    SET @inputText = REPLACE(@inputText, CHAR(9), '')
    SET @inputText = REPLACE(@inputText, CHAR(10), '')
    SET @inputText = REPLACE(@inputText, CHAR(13), '')

    RETURN (@inputText)
END
使用例:
DECLARE @htmlText NVARCHAR(8000)
SET @htmlText = '<body><div id=u><a href=http://passport.baidu.com/?login>ログイン</a></div>'
SELECT dbo.RemoveHtmlTags(@htmlText)
実行結果:
ログイン

文字列分割関数

指定された区切り文字で文字列を分割する関数です。
CREATE FUNCTION [dbo].[SplitString] (@inputString VARCHAR(2000), @delimiter VARCHAR(2))
RETURNS @resultTable TABLE (item VARCHAR(200))
AS
BEGIN
    WHILE CHARINDEX(@delimiter, @inputString) <> 0
    BEGIN
        INSERT INTO @resultTable (item) 
        VALUES (SUBSTRING(@inputString, 1, CHARINDEX(@delimiter, @inputString) - 1))
        SET @inputString = STUFF(@inputString, 1, CHARINDEX(@delimiter, @inputString), '')
    END
    INSERT INTO @resultTable (item) VALUES (@inputString)
    RETURN
END
使用例:
DECLARE @testString VARCHAR(20)
SET @testString = 'A,B,C,D,E'
SELECT * FROM dbo.SplitString(@testString, ',')
実行結果:
item
-----
A
B
C
D
E

日時処理関数

日付と時刻の操作は多くのアプリケーションで重要です。以下に便利な日時処理関数を紹介します。

星座判定関数

指定された日付から星座を判定する関数です。
CREATE FUNCTION dbo.GetConstellation (@inputDate DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN (
    SELECT MAX(star)
    FROM (
        SELECT '山羊座' AS star, 1 AS [month], 1 AS [day]
        UNION ALL SELECT '水瓶座', 1, 20
        UNION ALL SELECT '魚座', 2, 19
        UNION ALL SELECT '牡羊座', 3, 21
        UNION ALL SELECT '牡牛座', 4, 20
        UNION ALL SELECT '双子座', 5, 21
        UNION ALL SELECT '蟹座', 6, 22
        UNION ALL SELECT '獅子座', 7, 23
        UNION ALL SELECT '乙女座', 8, 23
        UNION ALL SELECT '天秤座', 9, 23
        UNION ALL SELECT '蠍座', 10, 24
        UNION ALL SELECT '射手座', 11, 22
        UNION ALL SELECT '山羊座', 12, 22
    ) stars
    WHERE DATEADD(month, [month] - 1, DATEADD(year, YEAR(@inputDate) - YEAR(0), 0)) + [day] - 1 =
    (
        SELECT MAX(DATEADD(month, [month] - 1, DATEADD(year, YEAR(@inputDate) - YEAR(0), 0)) + [day] - 1)
        FROM (
            SELECT '山羊座' AS star, 1 AS [month], 1 AS [day]
            UNION ALL SELECT '水瓶座', 1, 20
            UNION ALL SELECT '魚座', 2, 19
            UNION ALL SELECT '牡羊座', 3, 21
            UNION ALL SELECT '牡牛座', 4, 20
            UNION ALL SELECT '双子座', 5, 21
            UNION ALL SELECT '蟹座', 6, 22
            UNION ALL SELECT '獅子座', 7, 23
            UNION ALL SELECT '乙女座', 8, 23
            UNION ALL SELECT '天秤座', 9, 23
            UNION ALL SELECT '蠍座', 10, 24
            UNION ALL SELECT '射手座', 11, 22
            UNION ALL SELECT '山羊座', 12, 22
        ) stars
        WHERE @inputDate >= DATEADD(month, [month] - 1, DATEADD(year, YEAR(@inputDate) - YEAR(0), 0)) + [day] - 1
    )
    )
END
使用例:
SELECT dbo.GetConstellation('2010-05-04')
実行結果:
牡牛座

営業日計算関数

2つの日付間の営業日数を計算する関数です。
CREATE FUNCTION dbo.CalculateWorkDays
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @workDays INT, @i INT, @isBit BIT, @currentDate DATETIME
    IF @startDate > @endDate
       SELECT @isBit = 1, @currentDate = @startDate, @startDate = @endDate, @endDate = @currentDate
    ELSE
       SET @isBit = 0

    SELECT @i = DATEDIFF(Day, @startDate, @endDate) + 1, @workDays = @i / 7 * 5,
           @startDate = DATEADD(Day, @i / 7 * 7, @startDate)

    WHILE @startDate <= @endDate
    BEGIN
       SELECT @workDays = CASE WHEN (@@DATEFIRST + DATEPART(Weekday, @startDate) - 1) % 7  
       BETWEEN 1 AND 5 THEN @workDays + 1 ELSE @workDays END,
                            @startDate = @startDate + 1
    END
    RETURN(CASE WHEN @isBit = 1 THEN -@workDays ELSE @workDays END)
END
使用例:
SELECT dbo.CalculateWorkDays('2011-02-22', '2011-03-14') AS 営業日数
実行結果:
営業日数
-----------
15

データ変換関数

データ型変換や特殊な変換処理を行う関数です。

数字を漢字に変換

数字を漢字表記に変換する関数です。
CREATE FUNCTION [dbo].[ConvertNumberToChinese] (@number BIGINT)
RETURNS VARCHAR(20)
AS
BEGIN
  DECLARE @result VARCHAR(20), @symbol VARCHAR(2)
  IF @number < 0
    SELECT @symbol = '負', @result = '', @number = ABS(@number)
  ELSE
    SELECT @symbol = '', @result = ''

  WHILE @number <> 0
    SELECT @result = SUBSTRING('零壹貳參肆伍陸柒捌玖拾', @number % 10 + 1, 1) + @result, @number = @number / 10

  RETURN @symbol + @result
END
使用例:
SELECT dbo.ConvertNumberToChinese(12345678)
実行結果:
壹貳參肆伍陸柒捌

全角・半角変換関数

文字列の全角と半角を相互に変換する関数です。
CREATE FUNCTION ConvertFullWidthToHalfWidth
(  
  @inputString NVARCHAR(4000), -- 変換対象の文字列
  @convertToHalfWidth BIT             -- 0:全角→半角, 1:半角→全角
)
RETURNS NVARCHAR(4000)  
AS  
BEGIN  
    DECLARE @pattern NVARCHAR(8), @step INT, @i INT, @space INT  

    IF @convertToHalfWidth = 0  
       SELECT @pattern = N'%[！-～]%', @step = -65248, @inputString = REPLACE(@inputString, N'　', N'   ')
    ELSE  
       SELECT @pattern = N'%[!-~]%', @step = 65248, @inputString = REPLACE(@inputString, N'   ', N'　')

    SET @i = PATINDEX(@pattern COLLATE Latin1_General_BIN, @inputString)  

    WHILE @i > 0  
    SELECT @inputString = REPLACE(@inputString, SUBSTRING(@inputString, @i, 1), NCHAR(UNICODE(SUBSTRING(@inputString, @i, 1)) + @step))  
    , @i = PATINDEX(@pattern COLLATE Latin1_General_BIN, @inputString)  

    RETURN(@inputString)  
END
使用例:
SELECT dbo.ConvertFullWidthToHalfWidth('～～～～ｃａ！ｂ', 0)
実行結果:
~~~~ca!b

まとめ

これらのカスタム関数は、SQL Serverでのデータ処理を効率化する強力なツールです。文字列操作、日時処理、データ変換など、様々な場面で活用できます。実際のプロジェクトでは、これらの関数をベースにさらにカスタマイズして、特定のビジネス要件に対応した関数を開発することも可能です。
関数のパフォーマンスを考慮し、データ量が多い場合はカスタム関数の使用を控え、ストアドプロシージャやビューの利用を検討することも重要です。

タグ: SQL Server カスタム関数 T-SQL データベース 便利関数

6月1日 00:41 投稿

ホットタグ

©2026 異端開発室