便利な SQL の関数とか、構文とか、その他色々まとめてみる - その13 ( TRIM の自作 ) - - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　C# などの開発言語でのコーディングでは共通化などを行いますが、クエリでは共通関数化などをあまり見かけません。クエリ、ストアド関連のレビューしていると、下記のようなロジックをよく見かけます。

SET @TempValue = LTRIM(RTRIM(REPLACE.....

　少しでもストアドをすっきりさせるのであれば、下記のような関数を考えることができます。

CREATE FUNCTION dbo.TRIM(@value NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
    BEGIN
        RETURN LTRIM(RTRIM(@value)) 
    END

　さらに、文字列置換の機能を持たせるのあれば、下記のような関数が考えられます。(かなり適当ですが。)

CREATE FUNCTION dbo.TrimAndReplace(@value NVARCHAR(MAX),
                                   @string_pattern_list NVARCHAR(MAX) = NULL,
                                   @string_replacement_list NVARCHAR(MAX) = NULL) RETURNS NVARCHAR(MAX)
    BEGIN
        DECLARE @replaced_value NVARCHAR(MAX)
        SET @replaced_value = @value

        IF @string_pattern_list IS NOT NULL
            IF @string_replacement_list IS NOT NULL
                BEGIN
                    DECLARE @string_pattern     NVARCHAR(2000)
                    DECLARE @string_replacement NVARCHAR(2000)
		 
                    DECLARE string_pattern_list CURSOR 
                        FOR SELECT * FROM STRING_SPLIT(@string_pattern_list,',')
		
                    DECLARE string_replacement_list CURSOR 
                        FOR SELECT * FROM STRING_SPLIT(@string_replacement_list,',')

                    OPEN string_pattern_list  
				    OPEN string_replacement_list 

                    FETCH NEXT FROM string_pattern_list   
                        INTO @string_pattern

                    FETCH NEXT FROM string_replacement_list   
                        INTO @string_replacement

                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                            SET @replaced_value = REPLACE(@replaced_value,@string_pattern,@string_replacement)
                            FETCH NEXT FROM string_pattern_list   
                                INTO @string_pattern

                            FETCH NEXT FROM string_replacement_list   
                                INTO @string_replacement
                        END
                    CLOSE string_pattern_list
                    DEALLOCATE string_pattern_list
                    CLOSE string_replacement_list
                    DEALLOCATE string_replacement_list
                END
        RETURN LTRIM(RTRIM(@replaced_value)) 
    END

　使い方は下記の通りです。

DECLARE @value NVARCHAR(MAX)
DECLARE @string_pattern_list NVARCHAR(MAX)
DECLARE @string_replacement_list NVARCHAR(MAX)
DECLARE @result NVARCHAR(MAX)

SET @value = 'tonaide*hataraku!se#no&gijutsutekina!hitorigoto'
SET @string_pattern_list = '*,!,#,&,!'
SET @string_replacement_list = '_,_,_,_,_'
SET @result = dbo.TrimAndReplace(@value,@string_pattern_list,@string_replacement_list)

PRINT @result

f:id:koogucc11:20160823214848p:plain