This is test. !"#$%&''()-=^~\|@`[{;+:*]},<.>/?\_
↑こんなのが
%54%68%69%73%20%69%73%20%74%65%73%74%2e%20%21%22%23%24%25%26%27%28%29%2d%3d%5e%7e%5c%7c%40%60%5b%7b%3b%2b%3a%2a%5d%7d%2c%3c%2e%3e%2f%3f%5c%5f
↑こんな感じに変換されます。
ただし制限事項ありです。
- 入力文字はASCII文字限定です。日本語は入力不可です。
- Outputは最大4000文字です。それを超える場合の動作は分かりません (^_^;
--ORIGINAL VALUE DECLARE @str VARCHAR(4000) SELECT @str = 'This is test. !"#$%&''()-=^~\|@`[{;+:*]},<.>/?\_' --DECLARE AND INITIALIZE VARIABLES DECLARE @curr INT, @end INT, @out VARCHAR(4000) SELECT @curr = 0 , @end = LEN(@str), @out = '' --CONVERT EACH CHARACTER TO ENCODED CHARACTER WHILE @curr <= @end BEGIN DECLARE @target nvarchar(1), @unicode INT, @binary VARCHAR(10) SELECT @target = SUBSTRING(@str, @curr, @curr + 1) SELECT @unicode = UNICODE(@target) --http://yakumo80.blog33.fc2.com/blog-entry-409.html SELECT @binary = STUFF((master.dbo.fn_varbintohexstr( CAST(CAST(@unicode AS BIGINT) AS BINARY(1))) COLLATE Latin1_General_CI_AS_KS_WS ), 1, 2, '') SELECT @out = ISNULL(@out, '') + '%' + @binary SELECT @curr = @curr + 1 END --DISPLAY RESULT --E.G. '%54%68%69%73%20%69%73%20%74%65%73%74%2e%20%21%22%23%24%25%26%27%28%29%2d%3d%5e%7e%5c%7c%40%60%5b%7b%3b%2b%3a%2a%5d%7d%2c%3c%2e%3e%2f%3f%5c%5f' SELECT @out
ちなみにSQLの整形は以下のサイトを利用させてもらっています。