{"id":341,"date":"2023-09-16T10:44:55","date_gmt":"2023-09-16T02:44:55","guid":{"rendered":""},"modified":"2023-09-16T15:50:11","modified_gmt":"2023-09-16T07:50:11","slug":"sql%e5%b8%b8%e7%94%a8%e5%ad%97%e7%ac%a6%e4%b8%b2%e6%88%aa%e5%8f%96","status":"publish","type":"post","link":"https:\/\/mushiming.com\/341.html","title":{"rendered":"SQL\u5e38\u7528\u5b57\u7b26\u4e32\u622a\u53d6"},"content":{"rendered":"<p>1\u3001RTRIM()\u5220\u9664\u5de6\u8fb9\u51fa\u73b0\u7684\u5b57\u7b26\u4e32<\/p>\n<pre><code class=\"language-sql\">select LTRIM('abdcdabddee','abc') from dual;  <\/code><\/pre>\n<p>2\u3001RTRIM()\u5220\u9664\u53f3\u8fb9\u51fa\u73b0\u7684\u5b57\u7b26\u4e32<\/p>\n<pre><code class=\"language-sql\">select RTRIM('abdcdabddee','abc') from dual; <\/code><\/pre>\n<p>3\u3001\u5728\u5217\u7684\u5de6\u8fb9\u7c98\u8d340\u5b57\u7b26<\/p>\n<pre><code class=\"language-sql\">SELECT LPAD('100',8,'0') FROM emp;\nrpad('tech',2); \u5c06\u8fd4\u56de' te' <\/code><\/pre>\n<p>4\u3001\u5c06\u66ff\u6362\u5b57\u7b26\u6539\u4e3a\u4fee\u6539<\/p>\n<p>replace('\u66ff\u6362\u5b57\u7b26\u4e32', '\u66ff\u6362', '\u4fee\u6539')<\/p>\n<p>5\u3001 \u67e5\u627e\u5b57\u7b26\u4e32\u4f4d\u7f6e<\/p>\n<pre><code class=\"language-sql\">select INSTR('CORPORATE FLOOR','OR', 3, 2) as loc from dual  \n\nINSTR\uff08string,subString,position,ocurrence\uff09\u67e5\u627e\u5b57\u7b26\u4e32\u4f4d\u7f6e<\/code><\/pre>\n<p>\u89e3\u91ca\uff1astring\uff1a\u6e90\u5b57\u7b26\u4e32 subString\uff1a\u8981\u67e5\u627e\u7684\u5b50\u5b57\u7b26\u4e32 position\uff1a\u67e5\u627e\u7684\u5f00\u59cb\u4f4d\u7f6e ocurrence\uff1a\u6e90\u5b57\u7b26\u4e32\u4e2d\u7b2c\u51e0\u6b21\u51fa\u73b0\u7684\u5b50\u5b57\u7b26\u4e32instr('sss:aa','\uff1a') \uff0c\u8fd4\u56de:\u6240\u5728\u4f4d\u7f6e\u7d22\u5f15\u3002<\/p>\n<p>6\u3001\u5b57\u7b26\u4e32\u622a\u53d6<\/p>\n<pre><code class=\"language-sql\">select substr(to_char(sysdate, 'yyyy-mm-dd HH:mi:ss'), 12, 5) as time from dual   \nSUBSTR(string,start_position,[length])  <\/code><\/pre>\n<p>\u89e3\u91ca\uff1astring \u6e90\u5b57\u7b26\u4e32 &nbsp; start_position &nbsp; \u5f00\u59cb\u4f4d\u7f6e\uff08\u4ece0\u5f00\u59cb\uff09 length \u53ef\u9009\u9879\uff0c\u5b50\u5b57\u7b26\u4e32\u7684\u4e2a\u6570<\/p>\n<p>7\u3001\u62fc\u63a5\u5b57\u7b26\u4e32<\/p>\n<pre><code class=\"language-sql\">select concat('\u62fc\u63a5', '\u5b57\u7b26\u4e32') as str from dual   \/\/\u591a\u4e2a\u5219\u9700\u8981\u5d4c\u5957\nselect '\u62fc\u63a5'||'\u5b57\u7b26\u4e32' as str from dual  <\/code><\/pre>\n<p>8\u3001\u5b57\u7b26\u8f6c\u6570\u5b57<\/p>\n<pre><code class=\"language-sql\">to_number(LTRIM(MAX('007'),'0'))<\/code><\/pre>\n<p>9\u3001\u65e5\u671f\u6bd4\u8f83<\/p>\n<p>(1)\u3001\u5728XX\u4e4b\u524d<\/p>\n<pre><code class=\"language-sql\">\/\/to_date(\u5b57\u7b26,\u2018\u683c\u5f0f\u2019)   \nselect * from up_date where update &lt;= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')   <\/code><\/pre>\n<p>(2)\u3001\u8303\u56f4\u65f6\u95f4\u6bb5<\/p>\n<pre><code class=\"language-sql\">between date1  and date2;     \ndate1&gt;=to_date('','') and to_date('','')&lt;=date2 <\/code><\/pre>\n<p>(3)\u3001\u8fd4\u56de\uff1a2004-10-17 00:00:00.000 &nbsp;\u5411\u65e5\u671f\u52a0\u4e0a2\u5929&nbsp;<\/p>\n<pre><code class=\"language-sql\">select dateadd(day,2,'2004-10-15')<\/code><\/pre>\n<p>(4)\u3001datepart \u8fd4\u56de\u4ee3\u8868\u6307\u5b9a\u65e5\u671f\u7684\u6307\u5b9a\u65e5\u671f\u90e8\u5206\u7684\u6574\u6570<\/p>\n<pre><code class=\"language-sql\">SELECT DATEPART(month, '2004-10-15') --\u8fd4\u56de 10 <\/code><\/pre>\n<p>(5)\u3001datename \u8fd4\u56de\u4ee3\u8868\u6307\u5b9a\u65e5\u671f\u7684\u6307\u5b9a\u65e5\u671f\u90e8\u5206\u7684\u5b57\u7b26\u4e32<\/p>\n<pre><code class=\"language-sql\">SELECT datename(weekday, '2004-10-15') --\u8fd4\u56de\uff1a\u661f\u671f\u4e94 <\/code><\/pre>\n<p>(6)\u3001datediff \u8fd4\u56de\u8de8\u4e24\u4e2a\u6307\u5b9a\u65e5\u671f\u7684\u65e5\u671f\u548c\u65f6\u95f4\u8fb9\u754c\u6570<\/p>\n<pre><code class=\"language-sql\">select datediff(day,'2004-09-01','2004-09-18') --\u8fd4\u56de\uff1a17<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"1\u3001RTRIM()\u5220\u9664\u5de6\u8fb9\u51fa\u73b0\u7684\u5b57\u7b26\u4e32selectLTRIM('abdcdabddee','abc')fromdual;2\u3001RTRIM()\u5220\u9664\u53f3\u8fb9\u51fa\u73b0\u7684\u5b57\u7b26\u4e32selectRTRIM('abd...","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[82],"tags":[],"_links":{"self":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/341"}],"collection":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/comments?post=341"}],"version-history":[{"count":0,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/341\/revisions"}],"wp:attachment":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/media?parent=341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/categories?post=341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/tags?post=341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}