cool hit counter [SQL] Beware of string splicing leading to length explosion_Intefrankly

[SQL] Beware of string splicing leading to length explosion


Look at the code:

DECLARE @max VARCHAR(max)
SET @max='aaa...' --Here it is.8000 sizea
         +'bb' -- Connect avarchar Constants or variables

SELECT LEN(@max)

Don't assume it will return 8002, but 8000, select @max will only get 8000 a, the last two b gone. We know that the varchar (max) type is not limited by the number of characters, but why is this happening?

This actually has nothing to do with@max independent of the data type of the, Rather, it has to do with the type of data obtained after string splicing, Or rather, Related to data type inference for string constants。 (located) atSQL 2005 harmonySQL 2008(R2) in, Knock one.'a', The system will treat it asvarchar(1),'aa' thenvarchar(2),N'a' thennvarchar(1), but (not)'a...'( surpass8000 sizea) this (Cantonese),05 In will be when it istext,08 Then when it isvarchar(max), The data type of a constant or variable can be determined by the system functionSQL_VARIANT_PROPERTY gain, Example of use:

SELECT SQL_VARIANT_PROPERTY('a','BaseType')

For more information about this function, see SSMS Help orhttp://technet.microsoft.com/zh-cn/library/ms178550(v=sql.105).aspx

So here's the question., Since more than8000 Constants of up to three characters are automatically recognized by the system as big data types, No truncation will occur, Why did the splicing stop when it did?, This is becausevarchar(n)+varchar(n) or=varchar(n), The system automatically expands the data length when splicing, but does not change the data type(varchar(n) together withvarchar(max) should be considered as different data types), also becausevarchar(n) hit the targetn The maximum value taken is8000, consequentlyvarchar(x)+varchar(y) The maximum will only getvarchar(8000), properx+y>8000 time, Then there will be a truncation。

Going back to the example at the beginning of the article, It's clear.,'aaa...' harmony'bb' allvarchar(n), Splicing givesvarchar(8000), That is, the truncated8000 size'aaa...', So even if you assign it tovarchar(max) It won't help.。 in case'aaa...' multiple morea, Things are different again., This is when thetext perhapsvarchar(max)+varchar(n), as far as sth is concerned05, reportabletext together withvarchar Cannot be spliced, as far as sth is concerned08, will correctly get the truncation-free'aaa...bb', owing tovarchar(max)+varchar(n)=varchar(max)。

OK is here, I hope the ape friend in stitching SQL string when paying attention to this problem.


Recommended>>
1、The ultimate solution for asynchronous processing in WeChat applets asyncawait
2、GoogleChromeContinues to pull away from rivals with strong performance in browser market
3、google Microsoft corporation Ali proper name American Express Internship Interview
4、Getting Started with ZooKeeper
5、Python KNN algorithm to determine word similarity small case

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号