{"id":967,"date":"2021-01-20T00:32:01","date_gmt":"2021-01-19T15:32:01","guid":{"rendered":"https:\/\/skyit.biz\/?p=967"},"modified":"2021-01-25T01:39:56","modified_gmt":"2021-01-24T16:39:56","slug":"%e7%ac%ac4%e7%ab%a0%e5%8d%98%e4%b8%80%e8%a1%8c%e9%96%a2%e6%95%b0","status":"publish","type":"post","link":"https:\/\/skyit.biz\/?p=967","title":{"rendered":"\u7b2c4\u7ae0\t\u5358\u4e00\u884c\u95a2\u6570"},"content":{"rendered":"\n

4.2\u6587\u5b57\u95a2\u6570<\/p>\n\n\n\n

\u25a0\u5927\u6587\u5b57\u30fb\u5c0f\u6587\u5b57\u95a2\u6570
UPPER \u95a2\u6570 \u5168\u3066\u5927\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570
LOWER \u95a2\u6570 \u5168\u3066\u5c0f\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570
INITCAP \u95a2\u6570 \u5358\u8a9e\u306e\u5148\u982d\u6587\u5b57\u3092\u5927\u6587\u5b57\u306b\u30012\u6587\u5b57\u76ee\u4ee5\u964d\u306f\u5c0f\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570<\/p>\n\n\n\n

\u25a0\u6587\u5b57\u64cd\u4f5c\u95a2\u6570
CONCAT \u95a2\u6570 2\u3064\u306e\u6587\u5b57\u5217\u3092\u7d50\u5408\u3057\u3066\u623b\u3059\u95a2\u6570
SUBSTR \u95a2\u6570 M\u756a\u76ee\u306e\u6587\u5b57\u304b\u3089N\u6587\u5b57\u6587\u306e\u6587\u5b57\u5217\u3092\u623b\u3059\u95a2\u6570
LENGTH \u95a2\u6570 \u6587\u5b57\u6570\u3092\u623b\u3059\u95a2\u6570
INSTR \u95a2\u6570 \u6307\u5b9a\u3057\u305f\u6587\u5b57\u30d1\u30bf\u30fc\u30f3\u304c\u73fe\u308c\u308b\u4f4d\u7f6e\u3092\u623b\u3059\u95a2\u6570
LPAD \u95a2\u6570 N\u6587\u5b57\u306b\u6210\u308b\u3088\u3046\u306b\u5de6\u5074\u306b\u57cb\u3081\u8fbc\u307f\u6587\u5b57\u3092\u57cb\u3081\u8fbc\u3093\u3067\u623b\u3059\u95a2\u6570
RPAD \u95a2\u6570 N\u6587\u5b57\u306b\u6210\u308b\u3088\u3046\u306b\u53f3\u5074\u306b\u57cb\u3081\u8fbc\u307f\u6587\u5b57\u3092\u57cb\u3081\u8fbc\u3093\u3067\u623b\u3059\u95a2\u6570
TRIM \u95a2\u6570 \u524d\u5f8c\u306b\u6709\u308b\u524a\u9664\u6587\u5b57\u3092\u53d6\u308a\u9664\u3044\u3066\u623b\u3059\u95a2\u6570
REPLACE \u95a2\u6570 \u5909\u66f4\u524d\u6587\u5b57\u5217\u3092\u5909\u66f4\u5f8c\u6587\u5b57\u5217\u306b\u7f6e\u304d\u63db\u3048\u3066\u3001\u623b\u3059\u95a2\u6570<\/p>\n\n\n\n

\u25a0\u6570\u5024\u95a2\u6570
ROUND \u95a2\u6570 \u5c0f\u6570\u70b9\u4ee5\u4e0bn\u6841\u306b\u56db\u6368\u4e94\u5165\u3057\u3066\u623b\u3059\u95a2\u6570
TRANC \u95a2\u6570 \u5c0f\u6570\u70b9\u4ee5\u4e0bn\u6841\u306b\u5207\u308a\u6368\u3066\u3066\u623b\u3059\u95a2\u6570
MOD \u95a2\u6570 \u5f15\u6570n\u3092\u5f15\u6570m\u3067\u5272\u3063\u305f\u4f59\u308a\u3092\u623b\u3059\u95a2\u6570<\/p>\n\n\n\n

\u25a0\u65e5\u4ed8\u95a2\u6570
SYSDATE \u95a2\u6570
MONTHS_BETWEEN \u95a2\u6570
ADD_MONTHS \u95a2\u6570
NEXT_DAY
LAST_DAY
ROUND
TRUNC<\/p>\n\n\n\n

\u25a0CONCAT\u95a2\u6570
\u5f15\u6570\u3068\u3057\u3066\u3001\u53d7\u3051\u5165\u308c\u305f2\u3064\u306e\u6587\u5b57\u5217\u3092\u7d50\u5408\u3057\u3066\u623b\u3059\u95a2\u6570\u3067\u3042\u308b\u3002
\u9023\u7d50\u6f14\u7b97\u5b50||\u3092\u4f7f\u7528\u3057\u3066\u3001\uff12\u3064\u306e\u6587\u5b57\u5217\u3092\u7d50\u5408\u3057\u305f\u5834\u5408\u3068\u540c\u3058\u7d50\u679c\u3092\u623b\u3059\u3002<\/p>\n\n\n\n

CONCAT(\u6587\u5b57\u52171, \u6587\u5b57\u52172)
\u5f15\u6570\u306f\u3001\u6587\u5b57\u5217\u3067\u306a\u304f\u3066\u3082\u3001\u6570\u5024\u3084\u65e5\u4ed8\u3067\u3042\u3063\u3066\u3082\u3001\u6697\u9ed9\u7684\u306a\u5909\u63db\u3067\u6587\u5b57\u5217\u306b\u5909\u63db\u3055\u308c\u308b\u3002<\/p>\n\n\n\n

\u25a0REPLACE\u95a2\u6570
REPLACE\u95a2\u6570\u306f\u3001\u5f15\u6570\u3068\u3057\u3066\u3001\u53d7\u3051\u5165\u308c\u305f\u6587\u5b57\u5217\u306e\u3046\u3061\u3001\u5909\u66f4\u524d\u6587\u5b57\u5217\u3092\u5909\u66f4\u5f8c\u6587\u5b57\u5217\u306b\u7f6e\u304d\u63db\u3048\u305f\u6587\u5b57\u5217\u3092\u623b\u3059\u95a2\u6570\u3067\u3042\u308b\u3002<\/p>\n\n\n\n

REPLACE(\u6587\u5b57\u5217, \u5909\u66f4\u524d\u6587\u5b57\u5217,\u5909\u66f4\u5f8c\u6587\u5b57\u5217)<\/p>\n\n\n\n

\u4e3b\u306a\u6570\u5024\u95a2\u6570
\u25a0ROUND\u95a2\u6570
SELECT ROUND(12345.678, 1) AS A1
,ROUND(12345.678,0) AS A2
,ROUND(12345.678) AS A3
,ROUND(12345.678, -1) AS A4
,ROUND(12345.678, -2) AS A5
,TRUNC(ROUND(135.79, -1), -2) AS A6
FROM dual;<\/p>\n\n\n\n

A1<\/td>A2<\/td>A3<\/td>A4<\/td>A5<\/td>A6<\/td><\/tr>
12345.7<\/td>12346<\/td>12346<\/td>12350<\/td>12400<\/td>100<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

ROUND\u95a2\u6570\u306e\u5f15\u6570n\u304c\u6b63\u6570\u306e\u5834\u5408\u306f\u3001\u5c0f\u6570\u70b9\u4ee5\u4e0b\u3092\u56db\u6368\u4e94\u5165\u3059\u308b\u3002
\u5f15\u6570n\u304c\u8ca0\u6570\u306e\u5834\u5408\u306f\u3001-1\u306f\u3001\u4e00\u306e\u4f4d\u3092\u56db\u6368\u4e94\u5165\u3057\u3001-2\u306f\u3001\u5341\u306e\u4f4d\u304c\u56db\u6368\u4e94\u5165\u3055\u308c\u308b\u3002<\/p>\n\n\n\n

\u25a0TRUNC\u95a2\u6570
\u5f15\u6570\u306b\u6307\u5b9a\u3055\u308c\u305f\u6570\u5024\u3092\u5c0f\u6570\u70b9\u4ee5\u4e0bn\u6841\u306b\u5207\u6368\u3066\u3066\u623b\u3059\u95a2\u6570\u3067\u3042\u308b\u3002
SELECT TRUNC(12345.678, 2) AS B1
,TRUNC(12345.678, -1) AS B2
,TRUNC(12345.678) AS B3
FROM dual;<\/p>\n\n\n\n

B1<\/td>B2<\/td>B3<\/td><\/tr>
12345.67<\/td>12340<\/td>12345<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u25a0\u65e5\u4ed8\u5024\u306e\u57fa\u790e\u77e5\u8b58
\u65e5\u4ed8\u5024\u306f\u3001\u4e16\u7d00\u3001\u5e74\u3001\u6708\u3001\u65e5\u3001\u6642\u3001\u5206\u3001\u79d2\u3092\u8868\u3059\u5185\u90e8\u7684\u306a\u6570\u5024\u66f8\u5f0f\u3067\u683c\u7d0d\u3055\u308c\u308b\u3002
\u65e5\u4ed8\u5024\u306b\u306f\u3001\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u8868\u793a\u66f8\u5f0f\u304c\u3042\u308b\u3002
\u82f1\u8a9e\u74b0\u5883\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u8868\u793a\u66f8\u5f0f\u306f\u3001DD-MON-RR(\u65e5-\u6708-\u5e74)\u3000MON\u306f\u3001JANUARY\u3001FEBRARY\u306a\u3069\u82f1\u5358\u8a9e\u3067\u30bb\u30c3\u30c8\u3067\u304d\u308b\u3002
\u9006\u306b01\u308403\u306a\u3069\u306fMON\u3067\u6697\u9ed9\u306e\u5909\u63db\u304c\u3067\u304d\u306a\u3044\u305f\u3081\u3001\u30a8\u30e9\u30fc\u3068\u306a\u308b\u3002
\u65e5\u672c\u8a9e\u74b0\u5883\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u8868\u793a\u66f8\u5f0f\u306f\u3001RR-MM-DD(\u5e74-\u6708-\u65e5)
\u65e5\u4ed8\u5024\u306b\u5bfe\u3057\u3066\u52a0\u7b97\u30fb\u6e1b\u7b97\u306a\u3069\u3092\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/p>\n\n\n\n

MONTHS_BETWEEN\u95a2\u6570
\u5f15\u6570\u3068\u3057\u3066\u3001\u53d7\u3051\u5165\u308c\u305f2\u3064\u306e\u65e5\u4ed8\u9593\u306e\u6708\u6570\u3092\u623b\u3059\u95a2\u6570\u3067\u3042\u308b\u3002
1\u30f6\u6708\u4ee5\u4e0b\u306e\u5024\u306f\u3001\u5c0f\u6570\u3068\u3057\u3066\u623b\u3055\u308c\u308b\u3002 \u65e5\u4ed8\u306f\u3001\u6708\u6570\u306b\u7f6e\u63db\u3055\u308c\u308b\u3002<\/p>\n\n\n\n

MONTHS_BETWEEN(\u65e5\u4ed81, \u65e5\u4ed82)
\u65e5\u4ed81\u304c\u65e5\u4ed82\u3088\u308a\u3082\u524d\u306e\u65e5\u4ed8\u306e\u5834\u5408\u306f\u3001\u8ca0\u306e\u6570\u5024\u3092\u623b\u3059\u3002
\u65e5\u4ed81\u304c\u65b0\u3057\u3044\u65e5\u3001\u65e5\u4ed82\u304c\u53e4\u3044\u65e5\u306e\u7d44\u5408\u305b\u7d50\u679c\u306f\u3001\u6b63\u6570\u3068\u306a\u308b\u3002
\u65e5\u4ed81\u304c\u53e4\u3044\u65e5\u3001\u65e5\u4ed82\u304c\u65b0\u3057\u3044\u65e5\u306e\u7d44\u5408\u305b\u306f\u3001\u8ca0\u6570\u3068\u306a\u308b\u3002
\u4f8b\uff09
SELECT ID, NAME1 FROM KINZOKU_DB1
WHERE MONTHS_BETWEEN(SYSDATE, KINZOKU_YMD) >= 240;
\u52e4\u7d9a20\u5e74\u4ee5\u4e0a\u306e\u793e\u54e1\u3092\u62bd\u51fa\u3059\u308b\u3002<\/p>\n\n\n\n

\u25a0ADD_MONTHS\u95a2\u6570
\u5f15\u6570\u306b\u6307\u5b9a\u3057\u305f\u65e5\u4ed8\u306en\u30f6\u6708\u5f8c\u306e\u65e5\u4ed8\u3092\u623b\u3059\u95a2\u6570\u3067\u3042\u308b\u3002
\u5f15\u6570n\u306b\u8ca0\u6570\u3092\u6307\u5b9a\u3059\u308b\u3068\u3001n\u30f6\u6708\u524d\u306e\u65e5\u4ed8\u304c\u623b\u3055\u308c\u308b\u3002<\/p>\n\n\n\n

ADDMONTHS(\u65e5\u4ed8,n)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

ADD_MONTHS\u3067\u3001\u6708\u672b\u65e5\u4ed8\u306e\u5834\u5408\u3001\u524d\u5f8c\u306e\u6708\u3082\u6708\u672b\u3068\u306a\u308b
9\u670830\u65e5 -1 = 8\u670831\u65e5
30\u65e5\u3067\u306f\u306a\u304f\u3001\u305d\u306e\u6708\u306e\u672b\u65e5\u306e31\u65e5\u304c\u30bb\u30c3\u30c8\u3055\u308c\u308b\u3002<\/p>\n\n\n\n

\u30bb\u30c3\u30b7\u30e7\u30f3\u30bf\u30a4\u30e0\u30be\u30fc\u30f3\u306e\u73fe\u5728\u306e\u65e5\u4ed8\u3092\u623b\u3059\u95a2\u6570\u306f\u3001CURRENT_DATE\u3067\u3042\u308b\u3002
\u30bb\u30c3\u30b7\u30e7\u30f3\u304c\u52d5\u4f5c\u3057\u3066\u3044\u308b\u73fe\u5730\u306e\u65e5\u4ed8\u3092\u77e5\u308a\u305f\u3044\u5834\u5408\u306f\u3001CURRENT_DATE\u95a2\u6570\u3092\u4f7f\u3046\u3002<\/p>\n\n\n\n

\u25a0INSTR\u95a2\u6570<\/p>\n\n\n\n

INSTR(\u6587\u5b57\u5217 , \u691c\u7d22\u6587\u5b57\u5217) = \u6570\u5024\u3092\u623b\u3059<\/p>\n\n\n\n

SELECT INSTR(‘Oracle Server’, ‘er’, 1, 2) AS T1, INSTR(\u6587\u5b57\u5217\u3001\u691c\u7d22\u6587\u5b57\u3001\u958b\u59cb\u4f4d\u7f6e\u3001\u56de\u6570\uff09
INSTR(‘Oracle Server’, ‘er’) AS T2
FROM dual;<\/p>\n\n\n\n

T1<\/td>T2<\/td><\/tr>
12<\/td>9<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

4.2\u6587\u5b57\u95a2\u6570 \u25a0\u5927\u6587\u5b57\u30fb\u5c0f\u6587\u5b57\u95a2\u6570UPPER \u95a2\u6570 \u5168\u3066\u5927\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570LOWER \u95a2\u6570 \u5168\u3066\u5c0f\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570INITCAP \u95a2\u6570 \u5358\u8a9e\u306e\u5148\u982d\u6587\u5b57\u3092\u5927\u6587\u5b57\u306b\u30012\u6587\u5b57\u76ee\u4ee5\u964d\u306f\u5c0f\u6587\u5b57\u306b\u5909\u63db\u3057\u3066\u623b\u3059\u95a2\u6570 \u25a0\u6587 […]<\/p>\n","protected":false},"author":1,"featured_media":969,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[52,12],"tags":[],"class_list":{"0":"post-967","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-bronze-sql-12c","8":"category-oracle"},"_links":{"self":[{"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/posts\/967","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/skyit.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=967"}],"version-history":[{"count":1,"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/posts\/967\/revisions"}],"predecessor-version":[{"id":970,"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/posts\/967\/revisions\/970"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/skyit.biz\/index.php?rest_route=\/wp\/v2\/media\/969"}],"wp:attachment":[{"href":"https:\/\/skyit.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/skyit.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/skyit.biz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}