MSSQL 에서 SUBSTR INSTR 같은 기능을 어떻게 구현하면되나요? 0 8 1,455

by 실베 [SQL Query] [2021.05.11 15:17:44]


select

A

FROM B

 

! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000280 | 10000281 | 10000282 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000402 | 10000403 | 10000404 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000280 | 10000281 | 10000284 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000288 | 10000289 | 10000290 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000402 | 10000403 | 10000420 | 10000421 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000502 | 10000503 | 10000504 | 10000506 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000007 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000007 | 
! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000009 | 

 

 

다음과같은 데이터가 있다고 가정하고 여기서 | 기준으로 split 하여 7번째의 값인 10000003 만 뽑아 오고 싶은데

어떻게하면 처리할수있을까요?

by 마농 [2021.05.11 16:03:00]

모든 자료가 예시 자료와 같은 패턴이라면?
고정 자리수로 자르면 되지 않을까요?
SUBSTRING(a, 39, 8)


by 실베 [2021.05.11 16:57:01]

답변감사합니다. 예시자료의 데이터 길이가 다 같아서 그런데 실제 데이터의 길이는 다를수가 있습니다..


by 마농 [2021.05.11 17:11:02]

질문 시 좀 더 실제에 가깝게 예를 들어 주셔야 합니다.


by 마농 [2021.05.11 17:13:54]
-- Oracle 의 INSTR 은 4번째 인자로 몇번째 값을 가져올지 지정이 가능한데.
-- MSSQL 의 CHARINDEX 는 인자가 3개 뿐이라 지정이 안되어 불편하네요.
-- MSSQL --
SELECT a
     , x                           -- 6번째 구분자 위치
     , CHARINDEX('|', a, x+1) y    -- 7번째 구분자 위치
     , TRIM(SUBSTRING(a, x+1, CHARINDEX('|', a, x+1) - x - 1)) z
  FROM (SELECT a
             , CHARINDEX('|', a
             , CHARINDEX('|', a
             , CHARINDEX('|', a
             , CHARINDEX('|', a
             , CHARINDEX('|', a
             , CHARINDEX('|', a
               )+1)+1)+1)+1)+1
               ) x
          FROM b
        ) c
;

 


by 실베 [2021.05.11 17:29:53]

답변 감사합니다.. 오라클에선 간단히 처리되는데 mssql 에선 쿼리가 너무 복잡해지는군요..


by 마농 [2021.05.11 18:01:17]
-- Oracle --
SELECT a
     , INSTR(a, '|', 1, 6) x  -- 6번째 구분자 위치
     , INSTR(a, '|', 1, 7) y  -- 7번째 구분자 위치
     , TRIM(SUBSTR(a, INSTR(a, '|', 1, 6) + 1
                    , INSTR(a, '|', 1, 7) - INSTR(a, '|', 1, 6) - 1
                    )) z
  FROM b
;

 


by 뉴비디비 [2021.05.11 21:54:04]

MSSQL이 아직 정상적인 Split 은 안되는거 같고,  2016 이상이면  아쉬운대로 JSON 형식으로 가능할꺼 같아요. 

-- MSSQL 2016 이상 OPENJSON : 테이블 데이터 처리
WITH BB ( A )AS (
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000280 | 10000281 | 10000282 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000402 | 10000403 | 10000404 | '			   UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000280 | 10000281 | 10000284 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000288 | 10000289 | 10000290 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000402 | 10000403 | 10000420 | 10000421 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000017 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000502 | 10000503 | 10000504 | 10000506 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000007 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000007 | ' UNION ALL
	SELECT '! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000004 | 10000005 | 10000006 | 10000009 | ' 
)
SELECT 
	TRIM(JTbl.[value]) AS [7번째]
FROM BB
	CROSS APPLY OPENJSON ('["'+ REPLACE(A, '|', '","')  +'"]') as JTbl
WHERE [key] = 6  -- 0 부터 시작


-- 개별 문자열 일때
SELECT
    TRIM(JTbl.[value]) AS [값]
FROM
    OPENJSON('["'+ REPLACE('! | ! | ! | ! | 10000001 | 10000002 | 10000003 | 10000279 | 10000280 | 10000281 | 10000282 | ', '|', '","')  +'"]') as JTbl
WHERE [key] = 6 -- 0 부터 시작

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입