2015-06-05
SQLServer覚書 カンマ区切り文字列→行データ
カンマ区切りのデータを縦に並べるプロシージャを作ってみた。
CREATE PROCEDURE csvToRows @csv VARCHAR(MAX) AS BEGIN DECLARE @exit_flg TINYINT = 0 DECLARE @i INT = 1 DECLARE @imax INT DECLARE @buf VARCHAR(MAX) BEGIN TRY -- はじめにNULLや空白を回避 IF RTRIM(LTRIM(ISNULL(@csv,'')))='' BEGIN RAISERROR (N'NULLはダメ!', 18, 1) END CREATE TABLE #w_temp ( buf VARCHAR(MAX) ) SET @buf = @csv WHILE @exit_flg = 0 BEGIN SET @buf = SUBSTRING(@buf, @i , LEN(@buf)) SET @imax = CHARINDEX(',', @buf) IF @imax = 0 BEGIN INSERT INTO #w_temp SELECT SUBSTRING(@buf, 1, LEN(@buf)) SET @exit_flg = 1 END ELSE BEGIN INSERT INTO #w_temp SELECT SUBSTRING(@buf, 1, @imax - 1) SET @i = @imax + 1 END END SELECT 'OK' AS result, NULL AS err_msg, buf FROM #w_temp END TRY BEGIN CATCH SELECT 'NG' AS result, ERROR_MESSAGE() AS err_msg END CATCH END