IF OBJECT_ID (N'dbo.split') IS NOT NULL
DROP FUNCTION dbo.split
GO
CREATE FUNCTION dbo.split(@b varchar(8000))
RETURNS @t table
(a int identity(1,1)
,b varchar(8000)
)
as
begin
set @b = @b + ','
declare
@v0 varchar(8000), @v1 varchar(8000), @v2 varchar(8000), @v3 varchar(8000), @v4 varchar(8000), @v5 varchar(8000), @v6 varchar(8000), @v7 varchar(8000)
, @v8 varchar(8000), @v9 varchar(8000), @v10 varchar(8000), @v11 varchar(8000), @v12 varchar(8000), @v13 varchar(8000), @v14 varchar(8000), @v15 varchar(8000)
, @v16 varchar(8000), @v17 varchar(8000), @v18 varchar(8000), @v19 varchar(8000), @v20 varchar(8000), @v21 varchar(8000), @v22 varchar(8000), @v23 varchar(8000)
, @v24 varchar(8000), @v25 varchar(8000), @v26 varchar(8000), @v27 varchar(8000), @v28 varchar(8000), @v29 varchar(8000), @v30 varchar(8000), @v31 varchar(8000)
, @v32 varchar(8000), @v33 varchar(8000), @v34 varchar(8000), @v35 varchar(8000), @v36 varchar(8000), @v37 varchar(8000), @v38 varchar(8000), @v39 varchar(8000)
, @v40 varchar(8000), @v41 varchar(8000), @v42 varchar(8000), @v43 varchar(8000), @v44 varchar(8000), @v45 varchar(8000), @v46 varchar(8000), @v47 varchar(8000)
, @v48 varchar(8000), @v49 varchar(8000), @v50 varchar(8000), @v51 varchar(8000), @v52 varchar(8000), @v53 varchar(8000), @v54 varchar(8000), @v55 varchar(8000)
, @v56 varchar(8000), @v57 varchar(8000), @v58 varchar(8000), @v59 varchar(8000), @v60 varchar(8000), @v61 varchar(8000), @v62 varchar(8000), @v63 varchar(8000)
, @v64 varchar(8000), @v65 varchar(8000), @v66 varchar(8000), @v67 varchar(8000), @v68 varchar(8000), @v69 varchar(8000), @v70 varchar(8000), @v71 varchar(8000)
, @v72 varchar(8000), @v73 varchar(8000), @v74 varchar(8000), @v75 varchar(8000), @v76 varchar(8000), @v77 varchar(8000), @v78 varchar(8000), @v79 varchar(8000)
, @v80 varchar(8000), @v81 varchar(8000), @v82 varchar(8000), @v83 varchar(8000), @v84 varchar(8000), @v85 varchar(8000), @v86 varchar(8000), @v87 varchar(8000)
, @v88 varchar(8000), @v89 varchar(8000), @v90 varchar(8000), @v91 varchar(8000), @v92 varchar(8000), @v93 varchar(8000), @v94 varchar(8000), @v95 varchar(8000)
, @v96 varchar(8000), @v97 varchar(8000), @v98 varchar(8000), @v99 varchar(8000)
select
@v0 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v1 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v2 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v3 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v4 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v5 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v6 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v7 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v8 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v9 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v10 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v11 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v12 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v13 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v14 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v15 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v16 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v17 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v18 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v19 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v20 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v21 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v22 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v23 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v24 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v25 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v26 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v27 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v28 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v29 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v30 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v31 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v32 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v33 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v34 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v35 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v36 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v37 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v38 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v39 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v40 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v41 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v42 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v43 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v44 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v45 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v46 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v47 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v48 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v49 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v50 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v51 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v52 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v53 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v54 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v55 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v56 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v57 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v58 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v59 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v60 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v61 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v62 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v63 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v64 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v65 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v66 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v67 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v68 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v69 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v70 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v71 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v72 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v73 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v74 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v75 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v76 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v77 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v78 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v79 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v80 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v81 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v82 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v83 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v84 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v85 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v86 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v87 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v88 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v89 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v90 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v91 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v92 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v93 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v94 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v95 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v96 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v97 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v98 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
, @v99 = replace(left(@b, charindex (',',@b)),',',''),@b = stuff(@b,1,charindex (',',@b),'')
insert into @t values
(@v0),(@v1),(@v2),(@v3),(@v4),(@v5),(@v6),(@v7),(@v8),(@v9)
,(@v10),(@v11),(@v12),(@v13),(@v14),(@v15),(@v16),(@v17),(@v18),(@v19)
,(@v20),(@v21),(@v22),(@v23),(@v24),(@v25),(@v26),(@v27),(@v28),(@v29)
,(@v30),(@v31),(@v32),(@v33),(@v34),(@v35),(@v36),(@v37),(@v38),(@v39)
,(@v40),(@v41),(@v42),(@v43),(@v44),(@v45),(@v46),(@v47),(@v48),(@v49)
,(@v50),(@v51),(@v52),(@v53),(@v54),(@v55),(@v56),(@v57),(@v58),(@v59)
,(@v60),(@v61),(@v62),(@v63),(@v64),(@v65),(@v66),(@v67),(@v68),(@v69)
,(@v70),(@v71),(@v72),(@v73),(@v74),(@v75),(@v76),(@v77),(@v78),(@v79)
,(@v80),(@v81),(@v82),(@v83),(@v84),(@v85),(@v86),(@v87),(@v88),(@v89)
,(@v90),(@v91),(@v92),(@v93),(@v94),(@v95),(@v96),(@v97),(@v98),(@v99)
return
end
GO
declare @max_cnt int
, @a int
set @max_cnt = 1000
while (1=1) begin
select @a = count(*) from dbo.split('sys5,432,54,35,432,5,2436246,,236,32,5,34,543,25,5,43,5432,5,43,25,rscolsaa,a111111111a,v,d,d,d,d,d,d,d,d,d,d,d,d,d,,d,d,d,d,d,d,d,d,111111,,,,,,,,11111111111111111111111,111111111111111111111,11111111111111') where len(b) >0
set @max_cnt = @max_cnt - 1
if @max_cnt = 0 break
end
set statistics io on
set statistics profile on
set statistics io off
set statistics profile off
declare @a varchar(1000)
set @a = 'sys5,432,54,35,432,5,2436246,,236,32,5,34,543,25,5,43,5432,5,43,25,rscolsaa,a111111111a,v,d,d,d,d,d,d,d,d,d,d,d,d,d,,d,d,d,d,d,d,d,d,111111,,,,,,,,11111111111111111111111,111111111111111111111,11111111111111'
select *
from dbo.split(@a) where len(b) >0
SET STATISTICS IO OFF
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
CREATE TABLE TBLX
(
IDX INT
,VALS VARCHAR(1000)
)
GO
IF OBJECT_ID('NUMS') IS NOT NULL
DROP TABLE NUMS
GO
CREATE TABLE NUMS (NUM INT IDENTITY PRIMARY KEY)
GO
SET NOCOUNT ON
BEGIN TRAN
WHILE 1=1
BEGIN
INSERT NUMS DEFAULT VALUES
IF @@IDENTITY = 8000
BREAK
END
INSERT INTO TBLX VALUES (1,'1,2,2')
INSERT INTO TBLX VALUES (2,'11,22,2223')
COMMIT TRAN
-- 동작의 핵심은 NUMS 테이블을 이용해서 모든글자를 한자 한자 모두 조인 한 후
-- 시작점과 끝점을 찾는 것이다.
-- 또한 CHARINDEX 는 시작점을 재 셋팅 할 수 있다.
-- 1단계 1번에 들어있는 VALS 는 쉼표포함 6글자 이므로 6개만큼 복제 된다.
SELECT
*
FROM
TBLX A
INNER JOIN NUMS B
ON LEN(A.VALS) >= B.NUM
-- 2단계 구분자를 찾자
-- 1번 E 에서 살펴보면 2,4,6 에 구분자가 있다는 것을 알게 되었다.
-- 또한 시작점은 NUM 컬럼의 최소값을 쓰면 되고 마지막포인트는 E 값을 쓰면 된다.
SELECT
IDX, NUM, CHARINDEX (',',VALS+',',NUM) E, VALS
FROM
TBLX A
INNER JOIN NUMS B
ON LEN(A.VALS) >= B.NUM
-- 컴마의 개수로 그룹핑하면서 시작점(NUM 의 최소값), 끝점 찾기(E)
SELECT
IDX, MIN(NUM)S, CHARINDEX (',',VALS+',',NUM) E, MAX(VALS)
FROM
TBLX A
INNER JOIN NUMS B
ON LEN(A.VALS) >= B.NUM
GROUP BY IDX, CHARINDEX (',',VALS+',',NUM)
ORDER BY IDX, E
-- 한방에 끝
SELECT
IDX, SUBSTRING(VALS, S, E-S) VAL
FROM
(
SELECT
IDX, MIN(NUM) S, CHARINDEX (',',VALS+',',NUM) E, MAX(VALS) VALS
FROM
TBLX A
INNER JOIN NUMS B
ON LEN(A.VALS) >= B.NUM
GROUP BY IDX, CHARINDEX (',',VALS+',',NUM)
-- ORDER BY IDX, E
) A
ORDER BY 1
결과
-------
IDX VAL
--- ---
1 1
1 2
1 2
2 22
2 2223
2 11
IF OBJECT_ID('NUMS') IS NOT NULL
DROP TABLE NUMS
GO
CREATE TABLE NUMS (NUM INT IDENTITY PRIMARY KEY)
GO
SET NOCOUNT ON
BEGIN TRAN
WHILE 1=1
BEGIN
INSERT NUMS DEFAULT VALUES
IF @@IDENTITY = 8000
BREAK
END
COMMIT TRAN
GO
DECLARE @INPUTARRAY VARCHAR(8000)
SET @INPUTARRAY = '1,2222,2,3,1111444411'
SELECT SUBSTRING(VALS, S, E-S)
FROM
(
SELECT
MIN(NUM)S, CHARINDEX (',',VALS+',',NUM) E, MAX(VALS) VALS
FROM
(SELECT @INPUTARRAY VALS) A
INNER JOIN NUMS B
ON LEN(A.VALS) >= B.NUM
GROUP BY CHARINDEX (',',VALS+',',NUM)
) A
OPTION (ORDER GROUP)
GO
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
CREATE TABLE TBLX
(IDX INT
, C1 VARCHAR(100)
)
GO
INSERT INTO TBLX VALUES (1,'HAPPY')
INSERT INTO TBLX VALUES (2,'SQLER')
GO
IF OBJECT_ID ('USP_A') IS NOT NULL
DROP PROC USP_A
GO
CREATE PROC USP_A
(@INPUTARRAY VARCHAR(8000))
AS
SELECT *
FROM DBO.TBLX
WHERE IDX IN
(
SELECT CAST(SUBSTRING(VALS, S, E-S) AS INT)
FROM
(
SELECT
MIN(NUM)S, CHARINDEX (',',VALS+',',NUM) E, MAX(VALS) VALS
FROM
(SELECT @INPUTARRAY VALS) A
INNER JOIN DBO.NUMS B
ON LEN(A.VALS) >= B.NUM
GROUP BY CHARINDEX (',',VALS+',',NUM)
) A
)
OPTION (ORDER GROUP)
GO
EXEC DBO.USP_A '1,2'
GO
SELECT A.IDX,SUBSTRING(A.VALS,B.S,B.E-B.S) VAL
FROM TBLX A CROSS APPLY
(SELECT TOP (8000)
NUM S,
CHARINDEX(',',A.VALS+',',NUM + 1) E
FROM NUMS
WHERE NUM = CHARINDEX (',',','+A.VALS,NUM)
ORDER BY NUM) B
ORDER BY 1
DECLARE @INPUTARRAY VARCHAR(8000)
SET @INPUTARRAY = 'AAA,BBB.NB ,C, FD'
SELECT A.NUM, LTRIM(RTRIM(SUBSTRING(VALS, S, E - S))) CVAL
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) NUM
,MIN(NUM) S
,CHARINDEX(',', VALS + ',', NUM) E
,MAX(VALS) VALS
FROM (
SELECT @INPUTARRAY VALS
) A
INNER JOIN (
SELECT TOP 8000 ROW_NUMBER() OVER (
ORDER BY (
SELECT 1
)
) NUM
FROM SYSOBJECTS A
CROSS JOIN SYSOBJECTS B
) B ON LEN(A.VALS) >= B.NUM
GROUP BY CHARINDEX(',', VALS + ',', NUM)
) A
OPTION (ORDER GROUP)
GO