Fuwafuwa's memorandum

Fuwafuwa's memorandum

Data analysis, development, reading, daily feeling.
MENU

SQL server: 数値を3桁区切りにする

REPLACE(CONVERT(nvarchar,CONVERT(money, expression), 1), '.00', '')

SQL server: 数値型を判定

SELECT ISNUMERIC ( expression )
expressionが数値型の時1、それ以外は0を返す。

SQLserver: テーブル型をストアドプロシージャの引数に指定する

ストアドプロシージャの引数にテーブル型を指定し
そのテーブルの値を一時テーブルにインサートします。

最終的にインサートするための一時テーブルを作成します。

IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab

CREATE TABLE #t1
	( name NVARCHAR(10)
	, age INT )
  
ユーザー定義テーブル型でテーブルの構造を定義します。
(データベース>プログラミング>種類>ユーザー定義テーブル型)
CREATE TYPE test_table
  AS TABLE ( name NVARCHAR(256), age INT )
引数のテーブルの値を#tabにインサートするストアドプロシージャを用意します。
(データベース>プログラミング>ストアドプロシージャ)
USE [database_name]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE test_procedure
  @tab test_table READONLY

AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO #tab
	SELECT * FROM @tab

END
GO
ユーザー定義テーブル型の構造のテーブルを変数として用意し
テーブル変数に値を挿入し、それを引数として上記のストアドプロシージャを実行します。
-- ユーザー定義テーブル型で変数を定義
DECLARE @tab AS test_table

-- 上記の変数に値をインサート
INSERT INTO @tab (name, age)
 VALUES ( 'tanaka', 18)
       ,( 'aoki', 32)

-- ストアドプロシージャの実行
EXEC test_procedure @tab

-- 結果確認
SELECT * FROM #t1

SQL server: IDENTITYプロパティの初期値再設定

テーブルに対してDELETEを行うとオートインクリメントがにギャップが生じてしまうので。

DBCC CHECKIDENT ('[table_name]', RESEED, 0)
GO

SQL server: T-SQLでURLデコードを行うスカラー値関数

SQL上でデコードしたいことって多々ありますよね。
こんなの見つけました。
URL Decode in T-SQL https://www.codeproject.com/Articles/1005508/URL-Decode-in-T-SQL

CREATE FUNCTION [dbo].[UrlDecode]
(
    @URL NVARCHAR(4000)
)  
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @Position INT, @Base CHAR(16), @High TINYINT, @Low TINYINT, @Pattern CHAR(21)
    DECLARE @Byte1Value INT, @SurrogateHign INT, @SurrogateLow INT
    SELECT @Pattern = '%[%][0-9a-f][0-9a-f]%', @Position = PATINDEX(@Pattern, @URL)

    WHILE @Position > 0
    BEGIN
       SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
              @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
              @High = @High / 17 * 10 + @High % 17,
              @Low  = @Low  / 17 * 10 + @Low  % 17,
              @Byte1Value = 16 * @High + @Low
       IF @Byte1Value < 128 --1-byte UTF-8
          SELECT @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                 @Position = PATINDEX(@Pattern, @URL)
       ELSE IF @Byte1Value >= 192 AND @Byte1Value < 224 AND @Position > 0 --2-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,5) - 1)) * POWER(2,6),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                     @Position = PATINDEX(@Pattern, @URL)
       END
       ELSE IF @Byte1Value >= 224 AND @Byte1Value < 240 AND @Position > 0 --3-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,4) - 1)) * POWER(2,12),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
                     @Position = PATINDEX(@Pattern, @URL)
       END
       ELSE IF @Byte1Value >= 240 AND @Position > 0  --4-byte UTF-8
       BEGIN
           SELECT @Byte1Value = (@Byte1Value & (POWER(2,3) - 1)) * POWER(2,18),
                  @URL = STUFF(@URL, @Position, 3, ''),
                  @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,12),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
                     @URL = STUFF(@URL, @Position, 3, ''),
                     @Position = PATINDEX(@Pattern, @URL)
           IF @Position > 0
           BEGIN
              SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
                     @Low  = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
                     @High = @High / 17 * 10 + @High % 17,
                     @Low  = @Low  / 17 * 10 + @Low  % 17,
                     @Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1))
                     --,@URL = STUFF(@URL, @Position, 3, cast(@Byte1Value as varchar))
                     --,@Position = PATINDEX(@Pattern, @URL)

              SELECT @SurrogateHign = ((@Byte1Value - POWER(16,4)) & (POWER(2,20) - 1)) / POWER(2,10) + 13 * POWER(16,3) + 8 * POWER(16,2),
                     @SurrogateLow = ((@Byte1Value - POWER(16,4)) & (POWER(2,10) - 1)) + 13 * POWER(16,3) + 12 * POWER(16,2),
                     @URL = STUFF(@URL, @Position, 3, NCHAR(@SurrogateHign) + NCHAR(@SurrogateLow)),
                     @Position = PATINDEX(@Pattern, @URL)
           END
       END
    END
    RETURN REPLACE(@URL, '+', ' ')
END
使い方
SELECT dbo.UrlDecode ('http://domain.com/search?keyword=%E3%82%A4%E3%83%B3%E3%83%86%E3%83%AB%C2%AE+Core%E2%84%A2+M+%E3%83%97%E3%83%AD%E3%82%BB%E3%83%83%E3%82%B5%E3%83%BC%0D%0A')
=>http://domain.com/search?keyword=インテル® Core™ M プロセッサー

日本語と英語の他、中国語とアラビア語も利用できるようです。
ありがとうございました。

このカテゴリーに該当する記事はありません。