꿈꾸는 개발자, DBA 커뮤니티 구루비

mssql 숫자 뽑아내기

by 조신부리 [SQLServer] [2021.06.02 15:13:01]

안녕하세요.

mssql 질문입니다.

1>3
1>3>12
1>3>12>16
1>3>12>20
1>3>12>20>8
1>3>12>21
1>3>13
1>3>14
1>3>15
1>3>15>11
1>3>15>2

1>3>15>2>9

숫자들 만 뽑아내려고 합니다.

단순히 SUBSTRING 을 사용하면 자리수 때문에.....문제가 있더군요.

(추가)

현재 한개의 컬럼에 들어가 있는 값을 숫자들만으로 된 컬럼으로 뽑아내려고 합니다

1>3>12>20>8 ==> 1, 3, 12, 20, 8

1>3>15>2>9 ==> 1, 3, 15, 2, 9

위에서 4번째 값이 자릿수가 다르다보니 단순 substring()으로 해결이 안됩니다

by 마농 [2021.06.02 16:31:23]

"숫자만 뽑아낸다"는 표현은
그 결과를 어떻게 표현하고자 하는지 명확하지 않습니다.
원본 대비 원하는 결과표를 보여 주세요.

by 조신부리 [2021.06.02 16:35:49]

SELECT
SUBSTRING(SORT,1,1) AS sort1
, SUBSTRING(SORT,3,1) AS sort2
, SUBSTRING(SORT,5,2) AS sort3
, SUBSTRING(SORT,8,2) AS sort4
, SUBSTRING(SORT,11,2) AS sort5

통계쿼리를 작성하려고 합니다.

grouping_id를 얻기 위해 파싱이 필요한데...아직 아이디어 수준입니다.

하위값이 있으면 소계가 필요합니다.

3은 전체합

12는 자신을 포함한 하위 4개행 합

20은 자신을 포함한 하위 1개행 합

15는 자신을 포함한 하위 2개행 합

by 마농 [2021.06.02 17:00:04]

뭔가 개념적인 걸 표현한 듯 한데... 더 헷갈립니다.
그냥 원본 대비 결과표를 보여주세요.

by 조신부리 [2021.06.03 05:42:13]

원본에 (추가) 했습니다.

by 마농 [2021.06.03 08:44:02]

WITH t AS
(
SELECT '1>3' v
UNION ALL SELECT '1>3>12'
UNION ALL SELECT '1>3>12>16'
UNION ALL SELECT '1>3>12>20'
UNION ALL SELECT '1>3>12>20>8'
UNION ALL SELECT '1>3>12>21'
UNION ALL SELECT '1>3>13'
UNION ALL SELECT '1>3>14'
UNION ALL SELECT '1>3>15'
UNION ALL SELECT '1>3>15>11'
UNION ALL SELECT '1>3>15>2'
UNION ALL SELECT '1>3>15>2>9'
)
SELECT v
     , SUBSTRING(v, 0+1, a-0-1) v1
     , SUBSTRING(v, a+1, b-a-1) v2
     , SUBSTRING(v, b+1, c-b-1) v3
     , SUBSTRING(v, c+1, d-c-1) v4
     , SUBSTRING(v, d+1, e-d-1) v5
  FROM (SELECT v
             , CHARINDEX('>', x, 1) a
             , CHARINDEX('>', x 
             , CHARINDEX('>', x) + 1) b
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x) + 1) + 1) c
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x) + 1) + 1) + 1) d
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x 
             , CHARINDEX('>', x) + 1) + 1) + 1) + 1) e
          FROM (SELECT v, CONCAT(v, '>>>>>') x FROM t) a
        ) a
;

by 조신부리 [2021.06.03 09:02:06]

감사합니다.

제가 원하는 답입니다.

마농님도 복잡하게 풀어낼 수 밖에 없는거였네요.

by 마농 [2021.06.03 09:40:15]

좀 더 간결한 구문도 가능합니다.
다만, 행을 늘렸다가, 다시 줄이는 과정이 포함되다보니.
이런 과정이 없는 좀 더 복잡한 구문을 선택했습니다.

SELECT *
  FROM t
 CROSS APPLY OPENJSON(REPLACE(CONCAT('[', v, ']'), '>', ',')) a
 PIVOT (MIN(value) FOR [key] IN ([0], [1], [2], [3], [4])) a
;
 
SELECT v
     , MIN(CASE [key] WHEN 0 THEN value END) v1
     , MIN(CASE [key] WHEN 1 THEN value END) v2
     , MIN(CASE [key] WHEN 2 THEN value END) v3
     , MIN(CASE [key] WHEN 3 THEN value END) v4
     , MIN(CASE [key] WHEN 4 THEN value END) v5
     , MIN(CASE [key] WHEN 5 THEN value END) v6
  FROM t
 CROSS APPLY OPENJSON(REPLACE(CONCAT('[', v, ']'), '>', ',')) a
 GROUP BY v
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변