Varchar(8000) Concat

• kcj

  Mr or Mrs. 500

  Points: 540

  More actions

  April 16, 2015 at 11:45 am

  #317523

  I have two string variables each has Varchar(8000)

  Declare @VariableA varchar(8000)

  Declare @VariableB varchar(8000)

  How can I concatenate these two variables without converting them to varchar(max) ?

  If try select @VariableA + @VariableB , I only got 7999 characters…

  I am not sure why.. how can i fix this issue

  Thank you

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 16, 2015 at 12:44 pm

  #1791453

  My observation is that an implicit conversion happens when you concatenate the two strings but it tops out at 8000.

  Declare @VariableA varchar(100);

  Declare @VariableB varchar(100);

  SET @VariableA = REPLICATE('x',100);

  SET @VariableB = REPLICATE('y',100);

  SELECT LEN(@VariableA+@VariableB); --200

  GO

  Declare @VariableA varchar(8000);

  Declare @VariableB varchar(8000);

  SET @VariableA = REPLICATE('x',8000);

  SET @VariableB = REPLICATE('x',8000);

  SELECT LEN(@VariableA+@VariableB); --8000

  SELECT LEN(CAST(@VariableA AS varchar(max))+@VariableB); --16000

  I believe your only option is to cast as varchar(max).

  Here's an interesting article on this topic: SQL Server: String Concatenation and Implicit Conversion[/url]

  edit: added link to article

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  April 16, 2015 at 12:50 pm

  #1791456

  MSSS has no other char string datatype with it's max length greater then 8000 but Varchar(max). So you definetly need Varchar(max). Or XML or varbinary(max) if you prefer. What's wrong with simply converting to Varchar(max)?

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  April 16, 2015 at 1:39 pm

  #1791467

  kcj (4/16/2015)

  ---

  I have two string variables each has Varchar(8000)

  Declare @VariableA varchar(8000)

  Declare @VariableB varchar(8000)

  How can I concatenate these two variables without converting them to varchar(max) ?

  If try select @VariableA + @VariableB , I only got 7999 characters…

  I am not sure why.. how can i fix this issue

  Thank you

  You cannot do it!

  Quick question, how long are the character strings stored in those variables?

  😎

  You are right, no other option for a character type variable larger than 8000 bytes exists but the (MAX) (apart from old deferred and about to be discontinued blob types), so why no just use that, any input to other 8K or less in size is going to be truncated at the variable's declaration length.