TSQL string concatenation is not working within WHILE loop

stringtsql

Code bellow is not working, any ideas why?

declare @Counter int
set @Counter = 0
declare @ConcText nvarchar(1000)

while @Counter < 5
begin
    --set @ConcText = @ConcText + cast(@Counter as nvarchar(10)) + N' counter,'
    --set @ConcText = @ConcText + convert(nvarchar(10), @Counter) + N' counter,'
    set @ConcText = @ConcText + N' counter,'
    set @Counter = @Counter + 1
end
print @ConcText --<-- this is null, why  ??

Best Solution

See MSDN: + (String Concatenation) (Transact-SQL):

Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result.

So to get things work, it's a good practice to initiate varchar variables immediatly after declare:

DECLARE @ConcText NVARCHAR(1000)
SET @ConcText  = ''

Other way to handle NULL concat issue (in case you don't know if value is NULL or not) - ISNULL or COALESCE:

SET @ConcText = ISNULL(@ConcText, '') + N' counter,'
SET @ConcText = COALESCE(@ConcText, '') + N' counter,'

Related Solutions

Java – String concatenation: concat() vs “+” operator

No, not quite.

Firstly, there's a slight difference in semantics. If a is null, then a.concat(b) throws a NullPointerException but a+=b will treat the original value of a as if it were null. Furthermore, the concat() method only accepts String values while the + operator will silently convert the argument to a String (using the toString() method for objects). So the concat() method is more strict in what it accepts.

To look under the hood, write a simple class with a += b;

public class Concat {
    String cat(String a, String b) {
        a += b;
        return a;
    }
}

Now disassemble with javap -c (included in the Sun JDK). You should see a listing including:

java.lang.String cat(java.lang.String, java.lang.String);
  Code:
   0:   new     #2; //class java/lang/StringBuilder
   3:   dup
   4:   invokespecial   #3; //Method java/lang/StringBuilder."<init>":()V
   7:   aload_1
   8:   invokevirtual   #4; //Method java/lang/StringBuilder.append:(Ljava/lang/String;)Ljava/lang/StringBuilder;
   11:  aload_2
   12:  invokevirtual   #4; //Method java/lang/StringBuilder.append:(Ljava/lang/String;)Ljava/lang/StringBuilder;
   15:  invokevirtual   #5; //Method java/lang/StringBuilder.toString:()Ljava/lang/    String;
   18:  astore_1
   19:  aload_1
   20:  areturn

So, a += b is the equivalent of

a = new StringBuilder()
    .append(a)
    .append(b)
    .toString();

The concat method should be faster. However, with more strings the StringBuilder method wins, at least in terms of performance.

The source code of String and StringBuilder (and its package-private base class) is available in src.zip of the Sun JDK. You can see that you are building up a char array (resizing as necessary) and then throwing it away when you create the final String. In practice memory allocation is surprisingly fast.

Update: As Pawel Adamski notes, performance has changed in more recent HotSpot. javac still produces exactly the same code, but the bytecode compiler cheats. Simple testing entirely fails because the entire body of code is thrown away. Summing System.identityHashCode (not String.hashCode) shows the StringBuffer code has a slight advantage. Subject to change when the next update is released, or if you use a different JVM. From @lukaseder, a list of HotSpot JVM intrinsics.

C# – How would you count occurrences of a string (actually a char) within a string

If you're using .NET 3.5 you can do this in a one-liner with LINQ:

int count = source.Count(f => f == '/');

If you don't want to use LINQ you can do it with:

int count = source.Split('/').Length - 1;

You might be surprised to learn that your original technique seems to be about 30% faster than either of these! I've just done a quick benchmark with "/once/upon/a/time/" and the results are as follows:

Your original = 12s
source.Count = 19s
source.Split = 17s
foreach (from bobwienholt's answer) = 10s

(The times are for 50,000,000 iterations so you're unlikely to notice much difference in the real world.)

Best Solution

Related Solutions

Java – String concatenation: concat() vs “+” operator

C# – How would you count occurrences of a string (actually a char) within a string

Related Question