{"id":942,"date":"2017-05-20T17:33:24","date_gmt":"2017-05-20T08:33:24","guid":{"rendered":"https:\/\/oboki.net\/?p=942"},"modified":"2020-06-23T01:46:30","modified_gmt":"2020-06-22T16:46:30","slug":"aggr_concat-replace-chr-%ed%95%a8%ec%88%98%eb%a5%bc-%ec%9d%b4%ec%9a%a9%ed%95%9c-%ec%bf%bc%eb%a6%ac-%eb%a7%8c%eb%93%a4%ea%b8%b0","status":"publish","type":"post","link":"https:\/\/oboki.net\/workspace\/data-engineering\/database\/tibero\/aggr_concat-replace-chr-%ed%95%a8%ec%88%98%eb%a5%bc-%ec%9d%b4%ec%9a%a9%ed%95%9c-%ec%bf%bc%eb%a6%ac-%eb%a7%8c%eb%93%a4%ea%b8%b0\/","title":{"rendered":"[Tibero] AGGR_CONCAT, REPLACE, CHR \ud568\uc218\ub97c \uc774\uc6a9\ud55c \ucffc\ub9ac \ub9cc\ub4e4\uae30"},"content":{"rendered":"

AGGR_CONCAT, REPLACE, CHR \ud568\uc218\ub97c \uc774\uc6a9\ud55c \ucffc\ub9ac \ub9cc\ub4e4\uae30<\/h1>\n

\n AGGR_CONCAT<\/code>, REPLACE<\/code>, CHR<\/code> \ud568\uc218\ub85c \ud14c\uc774\ube14 \ub0b4\uc758 \ub370\uc774\ud130\ub97c \uc6d0\ud558\ub294\ub300\ub85c \ub098\uc5f4\ud560 \uc218 \uc788\ub2e4.\n<\/p><\/blockquote>\n

AGGR_CONCAT<\/code>\uc744 \uc774\uc6a9\ud558\uba74 \uc5ec\ub7ec \ud589\uc758 \ub370\uc774\ud130\ub97c \ud558\ub098\uc758 \uceec\ub7fc\uc73c\ub85c \ubaa8\uc544\uc11c \ubcf4\uc5ec\uc904 \uc218 \uc788\ub2e4.<\/p>\n

SELECT AGGR_CONCAT(LEVEL,',')\nFROM DUAL\nCONNECT BY LEVEL < 10\n<\/code><\/pre>\n
\uc704 \ucffc\ub9ac\ub97c \uc218\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \ud55c \ud589\uc5d0 \ubaa8\ub4e0 \ud589\uc758 \uac12\uc744 ,<\/code>\ub85c \uad6c\ubd84\ud574\uc11c \ubc18\ud658\ud574\uc900\ub2e4.<\/p>\n
AGGR_CONCAT(LEVEL,',')\n--------------------------------------------------------------------------------\n1,2,3,4,5,6,7,8,9\n\n1 row selected.\n<\/code><\/pre>\n
USER_TAB_COLUMNS<\/code>\uc640 \uac19\uc740 \ubdf0\uc5d0 \ud65c\uc6a9\ud558\uba74 \ud6a8\uacfc\uc801\uc778\ub370, \ud2b9\uc815 DATA_TYPE\ub9cc \uc120\ud0dd\ud574\uc11c \ucffc\ub9ac\ub97c \uc0dd\uc131\ud558\uac70\ub098 DATA_TYPE\uc5d0 \ub530\ub77c \ubcc4\ub3c4\uc758 \ucc98\ub9ac\ub97c \ud574\uc904 \uc218 \uc788\ub2e4.<\/p>\n
DBA_DATA_FILES<\/code> \ubdf0\uc5d0\uc11c DATA_TYPE\uc774 \uc22b\uc790\uc778 \uceec\ub7fc\ub9cc \uace8\ub77c\uc11c \uc870\ud68c\ud558\ub294 \ucffc\ub9ac\ub97c AGGR_CONCAT \ud568\uc218\ub97c \uc774\uc6a9\ud574\uc11c \ub9cc\ub4e4\uc5b4\ub0bc \uc218 \uc788\ub2e4. \ub2e4\uc74c\uacfc \uac19\uc774 \uc870\ud68c\ud558\uba74<\/p>\n
SELECT 'SELECT ' ||\n        AGGR_CONCAT(COLUMN_NAME,',') ||\n        ' FROM DBA_DATA_FILES'\nFROM\n        DBA_TAB_COLUMNS\nWHERE TABLE_NAME='DBA_DATA_FILES'\n        AND DATA_TYPE='NUMBER'\n<\/code><\/pre>\n
\uc544\ub798\uc640 \uac19\uc740 \uacb0\uacfc\ub97c \ubc1b\uc744 \uc218 \uc788\ub2e4.<\/p>\n
'SELECT'||AGGR_CONCAT(COLUMN_NAME,',')||'FROMDBA_DATA_FILES'\n--------------------------------------------------------------------------------\nSELECT FILE_ID,BYTES,BLOCKS,RELATIVE_FNO,MAXBYTES,MAXBLOCKS,INCREMENT_BY FROM DB\nA_DATA_FILES\n\n1 row selected.\n<\/code><\/pre>\n
linesize\uac00 \ucda9\ubd84\ud558\uc9c0 \uc54a\ub2e4\uba74, \uc8fc\uc5b4\uc9c4 linesize \ub9cc\ud07c \uac1c\ud589\uc774 \ub418\uc5b4 \ucd94\uac00\uc801\uc778 \uc218\uc791\uc5c5\uc774 \ud544\uc694\ud560 \uc218 \uc788\ub294\ub370 \uc774\ub54c REPLACE\uc640 CHR \ud568\uc218\ub97c \uc774\uc6a9\ud558\uba74 \uc801\ub2f9\ud788 \ucc98\ub9ac\ud558\uae30 \uc27d\uac8c \uac1c\ud589\uc744 \uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/p>\n
\ub2e4\uc74c\uacfc \uac19\uc774 ','<\/code>\ub97c ','||CHR(10)||CHR(13)<\/code> \uc73c\ub85c \ubcc0\uacbd\uc2dc\ucf1c\uc8fc\uba74 ,<\/code>\ub97c ,\\n<\/code>\uc73c\ub85c \ubcc0\uacbd\uc2dc\ud0b4\uc73c\ub85c\uc368, \uc758\ub3c4\uc801\uc73c\ub85c \uac1c\ud589\uc744 \uc2dc\ucf1c linesize\ub85c \uc778\ud574 \ubb38\uc790\uc5f4\uc774 \ub9dd\uac00\uc9c0\ub294 \ubb38\uc81c\ub97c \ud574\uacb0\ud560 \uc218 \uc788\ub2e4.<\/p>\n
SELECT 'SELECT ' ||\n        REPLACE(\n                AGGR_CONCAT(COLUMN_NAME,',')\n                ,',',','||CHR(10)||CHR(13)\n        )||\n        ' FROM DBA_DATA_FILES'\nFROM\n        DBA_TAB_COLUMNS\nWHERE TABLE_NAME='DBA_DATA_FILES'\n        AND DATA_TYPE='NUMBER'\n<\/code><\/pre>\n
\uc704 \ucffc\ub9ac\ub97c \uc218\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\ub97c \ubc18\ud658\ubc1b\ub294\ub2e4.<\/p>\n
'SELECT'||REPLACE(AGGR_CONCAT(COLUMN_NAME,','),',',','||CHR(10)||CHR(13))||'FROM\n--------------------------------------------------------------------------------\nSELECT FILE_ID,\nBYTES,\nBLOCKS,\nRELATIVE_FNO,\nMAXBYTES,\nMAXBLOCKS,\nINCREMENT_BY FROM DBA_DATA_FILES\n\n1 row selected.\n<\/code><\/pre>\n
\uc790\uc8fc \uc0ac\uc6a9\ud558\ub294 CHR<\/strong><\/p>\n
    \n
  • CHR(9)\n
      \n
    • \\t<\/code> (\ud0ed)<\/li>\n<\/ul>\n<\/li>\n
    • CHR(10)\n
        \n
      • \\n<\/code> (\uc904\ubc14\uafc8)<\/li>\n<\/ul>\n<\/li>\n
      • CHR(13)\n
          \n
        • \\r<\/code> (\ud589\uc758 \uc2dc\uc791)<\/li>\n<\/ul>\n<\/li>\n
        • CHR(38)\n
            \n
          • &<\/code> (\uc570\ud37c\uc0cc\ub4dc)<\/li>\n<\/ul>\n<\/li>\n
          • CHR(39)\n
              \n
            • '<\/code> (\ub530\uc634\ud45c)<\/li>\n<\/ul>\n<\/li>\n
            • CHR(44)\n
                \n
              • ,<\/code> (\uc27c\ud45c)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
                <\/p>\n
                <\/p>\n
                Oracle<\/code> \uc5d0\uc11c\ub294 LISTAGG<\/code>, Impala<\/code>\uc5d0\uc11c\ub294 GROUP_CONCAT<\/code> \ud568\uc218\ub97c \uc774\uc6a9\ud574\uc11c AGGR_CONCAT<\/code> \uacfc \uac19\uc740 \uae30\ub2a5\uc744 \ud560 \uc218 \uc788\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"
                AGGR_CONCAT, REPLACE, CHR \ud568\uc218\ub97c \uc774\uc6a9\ud55c \ucffc\ub9ac \ub9cc\ub4e4\uae30 AGGR_CONCAT, REPLACE, CHR \ud568\uc218\ub85c \ud14c\uc774\ube14 \ub0b4\uc758 \ub370\uc774\ud130\ub97c \uc6d0\ud558\ub294\ub300\ub85c \ub098\uc5f4\ud560 \uc218 \uc788\ub2e4. AGGR_CONCAT\uc744 \uc774\uc6a9\ud558\uba74 \uc5ec\ub7ec \ud589\uc758 \ub370\uc774\ud130\ub97c \ud558\ub098\uc758 \uceec\ub7fc\uc73c\ub85c \ubaa8\uc544\uc11c \ubcf4\uc5ec\uc904 \uc218 \uc788\ub2e4. SELECT AGGR_CONCAT(LEVEL,’,’) FROM DUAL CONNECT BY LEVEL < 10 \uc704 \ucffc\ub9ac\ub97c \uc218\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \ud55c \ud589\uc5d0 \ubaa8\ub4e0 \ud589\uc758 \uac12\uc744 ,\ub85c \uad6c\ubd84\ud574\uc11c \ubc18\ud658\ud574\uc900\ub2e4. AGGR_CONCAT(LEVEL,’,’) ——————————————————————————– 1,2,3,4,5,6,7,8,9 1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,8],"tags":[116,23],"class_list":["post-942","post","type-post","status-publish","format-standard","hentry","category-oracle","category-tibero","tag-sql","tag-tibero"],"_links":{"self":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/942","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/comments?post=942"}],"version-history":[{"count":3,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/942\/revisions"}],"predecessor-version":[{"id":1305,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/942\/revisions\/1305"}],"wp:attachment":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/media?parent=942"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/categories?post=942"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/tags?post=942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}