深入讲解SQL中的字符串拼接

白茶等風 發表於 2023-9-1 00:00:00

<p>
<span>一、概述</span></p>
<p>
相信大家在日常开发中，在sql语句中经常需要进行字符串拼接，以sqlserver，oracle，mysql三种数据库为例，因为这三种数据库具有代表性。</p>
<p>
<strong>sqlserver：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_503742">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql string">'123'</code><code class="sql plain">+</code><code class="sql string">'456'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<strong>oracle：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_86361">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql string">'123'</code><code class="sql plain">||</code><code class="sql string">'456'</code> <code class="sql keyword">from</code> <code class="sql plain">dual;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
或</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_195827">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">concat(</code><code class="sql string">'123'</code><code class="sql plain">,</code><code class="sql string">'456'</code><code class="sql plain">) </code><code class="sql keyword">from</code> <code class="sql plain">dual;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<strong>mysql：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_566505">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">concat(</code><code class="sql string">'123'</code><code class="sql plain">,</code><code class="sql string">'456'</code><code class="sql plain">);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<span><strong>注意：</strong></span>sql server中没有concat函数（sql server 2012已新增concat函数）。oracle和mysql中虽然都有concat，但是oracle中只能拼接2个字符串，所以建议用||的方式；mysql中的concat则可以拼接多个字符串。</p>
<p>
在sql server中的“+”号除了能够进行字符串拼接外，还可以进行数字运算，在进行字符串拼接时要小心使用。下面以“users”表为例，进行详细分析：</p>
<p>
<img title="深入讲解SQL中的字符串拼接" alt="深入讲解SQL中的字符串拼接" src="https://zhuji.jb51.net/uploads/img/202305/69198364431bc5c370406ba80dc0ad66.jpg"></p>
<p>
<span><strong>二、数字 + 字符串</strong></span></p>
<p>
<strong>2.1 int + varchar</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_299900">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">id + place </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">id = 1; //提示错误“在将 </code><code class="sql keyword">varchar</code> <code class="sql plain">值 </code><code class="sql string">'bzz'</code> <code class="sql plain">转换成数据类型 </code><code class="sql keyword">int</code> <code class="sql plain">时失败”</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">select</code> <code class="sql plain">id + place </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">id = 5; //提示错误“在将 </code><code class="sql keyword">varchar</code> <code class="sql plain">值 </code><code class="sql string">'102.34'</code> <code class="sql plain">转换成数据类型 </code><code class="sql keyword">int</code> <code class="sql plain">时失败”</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_814781">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">id + place </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">id = 4; //返回</code><code class="sql keyword">int</code> <code class="sql plain">“105”</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<strong>2.2 decimal + varchar</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_671709">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">*, id + cost </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">id = 4 </code><code class="sql color1">or</code> <code class="sql plain">id = 5; //返回</code><code class="sql keyword">decimal</code> <code class="sql plain">“102.98”和“104.30”</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_640151">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">*, place + cost </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">id = 1; //提示错误“从数据类型 </code><code class="sql keyword">varchar</code> <code class="sql plain">转换为 </code><code class="sql keyword">numeric</code> <code class="sql plain">时出错。”</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
由此可见，系统会将字符串varchar类型转化为int，若不能转换则提示错误，转换成功则进行数字计算。</p>
<p>
<span><strong>三、数字 + 数字</strong></span></p>
<p>
数字指的是int、decimal等类型。数字 + 数字，则进行数字相加，若某字段为null，则计算结果为null。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_869304">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">*, uage + cost </code><code class="sql keyword">as</code> <code class="sql string">'uage + cost'</code> <code class="sql keyword">from</code> <code class="sql plain">users</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<img title="深入讲解SQL中的字符串拼接" alt="深入讲解SQL中的字符串拼接" src="https://zhuji.jb51.net/uploads/img/202305/7f73e24c481396439db26aaa18bf5d4b.jpg"></p>
<p>
<span><strong>四、字符串 + 字符串</strong></span></p>
<p>
字符串 + 字符串，则直接进行拼接。若某字段为null，则计算结果为null。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_579477">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">*, uname + place </code><code class="sql keyword">as</code> <code class="sql string">'uname + place'</code> <code class="sql keyword">from</code> <code class="sql plain">users</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<img title="深入讲解SQL中的字符串拼接" alt="深入讲解SQL中的字符串拼接" src="https://zhuji.jb51.net/uploads/img/202305/60f5c78c42436f03f46bb83d4c6b5338.jpg"></p>
<p>
<span><strong>五、使用cast和convert函数进行类型转换</strong></span></p>
<p>
通过上述实例，可以看出若要使用“+”进行字符串拼接或数字计算，最稳妥的方法是进行类型转换。</p>
<ul>
<li>
<code>cast()</code>函数可以将某种数据类型的表达式转化为另一种数据类型</li>
<li>
<code>convert()</code>函数也可以将制定的数据类型转换为另一种数据类型</li>
</ul>
<p>
<strong>要求：</strong>将“678”转化为数值型数据，并与123相加进行数学运算。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_861282">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql color2">cast</code><code class="sql plain">(</code><code class="sql string">'678'</code> <code class="sql keyword">as</code> <code class="sql keyword">int</code><code class="sql plain">) + 123;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">select</code> <code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">int</code><code class="sql plain">, </code><code class="sql string">'678'</code><code class="sql plain">) + 123;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<strong>要求：</strong>id列和place列进行字符串拼接。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_564138">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">*, </code><code class="sql color2">convert</code><code class="sql plain">(</code><code class="sql keyword">varchar</code><code class="sql plain">(10), id) + place </code><code class="sql keyword">from</code> <code class="sql plain">users;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<span><strong>字符串拼接后的字符串不能简单作为“筛选字段”</strong></span></p>
<p>
有时，需要列a = 变量1，列b = 变量2的筛选，为了简化sql语句列a + 列b = 变量1 + 变量2。这种方法并不完全准确</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_69877">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">uname + place = </code><code class="sql string">'aabzz'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<img title="深入讲解SQL中的字符串拼接" alt="深入讲解SQL中的字符串拼接" src="https://zhuji.jb51.net/uploads/img/202305/aaf1d80f047017a4afdc3874653cf5ae.jpg"></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_103407">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">uname = </code><code class="sql string">'aa'</code> <code class="sql color1">and</code> <code class="sql plain">place = </code><code class="sql string">'bzz'</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<img title="深入讲解SQL中的字符串拼接" alt="深入讲解SQL中的字符串拼接" src="https://zhuji.jb51.net/uploads/img/202305/d41bdf5758f59d12bdc9e9a9eae2a148.jpg"></p>
<p>
为了防止上述情况的发生，可以再列a和列b之间加上一个较特殊的字符串。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_777223">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">users </code><code class="sql keyword">where</code> <code class="sql plain">uname + </code><code class="sql string">'rain@&%$man'</code> <code class="sql plain">+ place = </code><code class="sql string">'aa'</code> <code class="sql plain">+ </code><code class="sql string">'rain@&%$man'</code> <code class="sql plain">+ </code><code class="sql string">'bzz'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是关于sql中字符串拼接的全部内容了，希望本文的内容对大家的学习或者使用sql能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：http://www.cnblogs.com/rainman/p/6203065.html</p>

頁: [1]

琼殿技术论坛's Archiver

深入讲解SQL中的字符串拼接