blocks|key|225289|text|CONCAT是SQL+Server2012的新功能。您给出的链接清楚地说明了这一点，它不是以前版本中的函数，包括2008+R2。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|225290|在文档树中可以看到它是SQL+Server+2012的一部分：|225291|SQL+Server+2012++
Product+Documentation++
Books+Online+for+SQL+Server+2012++
Database+Engine++
++Transact-SQL+Reference+(Database+Engine)++
++++Built-in+Functions+(Transact-SQL)++
++++++String+Functions+(Transact-SQL)++|code-block|syntax|javascript|225292|编辑+Martin+Smith很有帮助地指出了SQL+Server+provides+an+implementation+of+ODBC's+CONCAT+function。|BOLD|225293|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb630290.aspx^0|0|6|0|0|0|0|2|1Z|6|N|1R|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|12|8|@$9|13|A|14|B|O]|$9|15|A|16|B|C]]|D|@$9|17|A|18|1|19]]|E|$]]|$1|P|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|Q|$R|$5|S|T|U|E|$V|W]]]]

<code>CONCAT</code> is new to SQL Server 2012. The link you gave makes this clear, it is not a function on Previous Versions, including 2008 R2.

That it is part of SQL Server 2012 can be seen in the document tree:

<pre><code>SQL Server 2012 
Product Documentation 
Books Online for SQL Server 2012 
Database Engine 
 Transact-SQL Reference (Database Engine) 
 Built-in Functions (Transact-SQL) 
 String Functions (Transact-SQL) 
</code></pre>

EDIT Martin Smith helpfully points out that <a href="http://msdn.microsoft.com/en-us/library/bb630290.aspx" rel="noreferrer">SQL Server provides an implementation of ODBC's <code>CONCAT</code> function</a>.

blocks|key|228180|text|只是为了完整性-在SQL2008中，您将使用加号%2B运算符来执行字符串连接。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|228181|请看一下带有示例代码的MSDN+reference。从SQL2012开始，您可能希望使用新的CONCAT+function。|228182|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms177561.aspx|1|http://msdn.microsoft.com/en-us/library/hh231515.aspx^0|O|1|0|B|E|0|1A|F|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@]|D|@$9|V|A|W|1|X]|$9|Y|A|Z|1|10]]|E|$]]|$1|H|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

Just for completeness - in SQL 2008 you would use the plus <code>+</code> operator to perform string concatenation. 

Take a look at the <a href="http://msdn.microsoft.com/en-us/library/ms177561.aspx" rel="noreferrer">MSDN reference</a> with sample code. Starting with SQL 2012, you may wish to use the new <a href="http://msdn.microsoft.com/en-us/library/hh231515.aspx" rel="noreferrer">CONCAT function</a>.

blocks|key|225332|text|我建议您先转换所有列，然后再合并它们|type|unstyled|depth|inlineStyleRanges|entityRanges|data|225333|cast('data1'+as+varchar)+%2B+cast('data2'+as+varchar)+%2B+cast('data3'+as+varchar)|code-block|syntax|javascript|225334|这对你来说应该是可行的。|225335|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I suggest you cast all columns before you concat them

<pre><code>cast('data1' as varchar) + cast('data2' as varchar) + cast('data3' as varchar)
</code></pre>

This should work for you.

blocks|key|228204|text|如上所述，在SQL+Server+2012之前不支持CONCAT。但是，您可以按照建议简单地使用%2B运算符进行连接。但请注意，如果第一个操作数是一个数字，则此操作符将抛出错误，因为它认为将是加法而不是连接。要解决此问题，只需在前面添加“”即可。例如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|228205|someNumber+%2B+'someString'+%2B+....+%2B+lastVariableToConcatenate|code-block|syntax|javascript|228206|将引发错误，但''+%2B+someNumber+%2B+'someString'+%2B+......将正常工作。|offset|length|style|CODE|228207|此外，如果有两个要连接的数字，请确保在它们之间添加'‘，如下所示|228208|....+%2B+someNumber+%2B+''+%2B+someOtherNumber+%2B+.....|228209|entityMap^0|0|0|7|13|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

CONCAT, as stated, is not supported prior to SQL Server 2012. However you can concatenate simply using the + operator as suggested. But beware, this operator will throw an error if the first operand is a number since it thinks will be adding and not concatenating. To resolve this issue just add '' in front. For example

<pre><code>someNumber + 'someString' + .... + lastVariableToConcatenate
</code></pre>

will raise an error BUT <code>'' + someNumber + 'someString' + ......</code> will work just fine. 

Also, if there are two numbers to be concatenated make sure you add a '' between them, like so 

<pre><code>.... + someNumber + '' + someOtherNumber + .....
</code></pre>

blocks|key|225356|text|SQL+Server2012+CONCAT函数的替换近似中的NULL安全删除|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|225357|SQL+Server+2012|225358|SELECT+CONCAT(data1,+data2)|CODE|225359|SQL+2012之前版本(两个解决方案)|225360|SELECT+{fn+CONCAT(ISNULL(data1,+''),+ISNULL(data2,+''))}|225361|SELECT+ISNULL(CAST(data1+AS+varchar(MAX)),+'')+%2B+ISNULL(CAST(data2+AS+varchar(MAX)),+'')|225362|这两个解决方案整理了其他海报提出的几个优秀的答案和警告，包括@Martin+Smith，@Svish和@vasin1987。|225363|这些选项将NULL添加到''+(空字符串)转换以实现安全的NULL处理，同时考虑到与特定操作数相关的%2B运算符的不同行为。|225364|注ODBC+Scaler+Function解决方案被限制为2个参数，而%2B+operator方法可根据需要扩展到多个参数。|225365|另请注意@Swifty发现的关于varchar(MAX)修复的默认varchar大小的潜在问题。|225366|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql^0|U|8|0|0|F|0|0|R|0|R|0|0|K|0|0|1K|0|1K|0|0|2G|0|2G|0|0|1Q|0|0|1O|5|4|C|2|T|4|1E|1|0|0|10|11|N|Z|1|1|K|0|0|0|1C|G|C|X|7|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|J]]|D|@]|E|$]]|$1|K|3|L|5|6|7|1H|8|@$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1K|8|@$9|1L|A|1M|B|C]|$9|1N|A|1O|B|J]]|D|@]|E|$]]|$1|O|3|P|5|6|7|1P|8|@$9|1Q|A|1R|B|C]|$9|1S|A|1T|B|J]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1U|8|@$9|1V|A|1W|B|C]]|D|@]|E|$]]|$1|S|3|T|5|6|7|1X|8|@$9|1Y|A|1Z|B|C]|$9|20|A|21|B|J]|$9|22|A|23|B|J]|$9|24|A|25|B|J]|$9|26|A|27|B|J]]|D|@]|E|$]]|$1|U|3|V|5|6|7|28|8|@$9|29|A|2A|B|C]|$9|2B|A|2C|B|C]|$9|2D|A|2E|B|J]]|D|@$9|2F|A|2G|1|2H]]|E|$]]|$1|W|3|X|5|6|7|2I|8|@$9|2J|A|2K|B|C]|$9|2L|A|2M|B|J]|$9|2N|A|2O|B|J]]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|2P|8|@]|D|@]|E|$]]]|Z|$10|$5|11|12|13|E|$14|15]]]]

NULL safe drop in replacement approximations for SQL Server 2012 CONCAT function

SQL Server 2012:

<code>SELECT CONCAT(data1, data2)</code>

PRE SQL 2012 (Two Solutions):

<code>SELECT {fn CONCAT(ISNULL(data1, ''), ISNULL(data2, ''))}</code>

<code>SELECT ISNULL(CAST(data1 AS varchar(MAX)), '') + ISNULL(CAST(data2 AS varchar(MAX)), '')</code>

These two solutions collate several excellent answers and caveats raised by other posters including @Martin Smith, @Svish and @vasin1987.

These options add <code>NULL</code> to <code>''</code> (empty string) casting for safe <code>NULL</code> handling while accounting for the varying behaviour of the <code>+</code> operator pertaining to specific operands.

Note the <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql" rel="noreferrer">ODBC Scaler Function</a> solution is limited to 2 arguments whereas the <code>+</code> operator approach is scalable to many arguments as needed.

Note also the potential issue identified by @Swifty regarding the default <code>varchar</code> size here remedied by <code>varchar(MAX)</code>.

blocks|key|2884444|text|(city+%2B+',+'+%2B+state+%2B+'+'+%2B+zip)+as+ctstzip+for+select
(city+%2B+',+'+%2B+state+%2B+'+'+%2B+zip)+for+insert|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2884445|仅当任何字段类型与其他字段类型不同时才进行强制转换或转换。|unstyled|2884446|在插入时，值需要放在需要插入的正确位置。使用"as“会给出一个错误。|2884447|即|2884448|Insert+into+testtable+(ctstzip)+Values+((city+%2B+',+'+%2B+state+%2B+'+'+%2B+zip))|2884449|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>(city + ', ' + state + ' ' + zip) as ctstzip for select
(city + ', ' + state + ' ' + zip) for insert
</code></pre>

Only cast or convert if any field type is different from others.

On insert the value needs to be in the correct spot you need it be inserted. Using "as" will give you an error.

i.e.

<pre><code>Insert into testtable (ctstzip) Values ((city + ', ' + state + ' ' + zip))
</code></pre>

blocks|key|228263|text|是的，该函数不在SQL2008中。您可以使用cast操作来执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|228264|例如，我们有employee表，而您想要带有applydate的name。|offset|length|style|CODE|228265|所以你可以使用|228266|Select+++cast(name+as+varchar)+%2B+cast(applydate+as+varchar)+from+employee|code-block|syntax|javascript|228267|它将在concat函数不工作的情况下工作。|228268|entityMap^0|0|6|8|M|9|W|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]|$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

Yes the function is not in sql 2008. You can use the cast operation to do that.

For example we have <code>employee</code> table and you want <code>name</code> with <code>applydate</code>.

so you can use

<pre><code>Select cast(name as varchar) + cast(applydate as varchar) from employee
</code></pre>

It will work where concat function is not working.

blocks|key|2884577|text|您可以在想要连接的字符串之间使用'%2B‘|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2884578|SELECT+string1+%2B+string2|code-block|syntax|javascript|2884579|如果其中一列出现转换错误，比如其中一列是整型列，则应该在连接列之前对其进行转换，例如|2884580|SELECT+(CONVERT(nvarchar,+intColumn)+%2B+string2|2884581|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can use '+' between the strings that you want to concat like
<pre><code>SELECT string1 + string2
</code></pre>
If one of those give conversion error like if one of the columns is an int column you should cast it before concatenating the columns like
<pre><code>SELECT (CONVERT(nvarchar, intColumn) + string2
</code></pre>

I was looking for a <code>CONCAT</code> function in SQL Server 2008 R2. I <a href="http://msdn.microsoft.com/en-us/library/hh231515.aspx">found the link for this function</a>. But when I use this function, it gives the following error:

<blockquote>
 Msg 195, Level 15, State 10, Line 7 
 'CONCAT' is not a recognized built-in function name.
</blockquote>

Does the <code>CONCAT</code> function exists in SQL Server 2008 R2?

If not, how do I concatenate strings in SQL Server 2008 R2?

How do I use the CONCAT function in SQL Server 2008 R2?

SQL Server

我在寻找SQL Server2008 R2中的CONCAT函数。I 。但是当我使用这个函数时，它给出了以下错误：消息195，级别15，状态10，第7行“‘CONCAT”不是可识别的内置函数名。SQL Server2008 R2中是否存在CONCAT函数？如果不是，如何在SQL Server2008 R2中连接字符串？

问如何使用SQL Server2008 R2中的CONCAT函数？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL Server2008 R2中的CONCAT函数？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL Server2008 R2中的CONCAT函数？
EN