CODEWITHSUNDEEP
sqlt-sql
John S
John S

Reputation: 8349

Why does this TSql return a blank or null value?

Why does the @result value in the code below print out a blank string? I would expect it to concatenate with the previous result.

DECLARE @size int
DECLARE @string nvarchar(10)
DECLARE @result nvarchar(10)
SELECT  @string = '12345abc123'
DECLARE @count int, @total int
SELECT
    @total = LEN(@string),
    @count = 1

WHILE @count <= @total
    BEGIN
    SELECT  @result = SUBSTRING(@string, @count, 1) + '-'+ @result 
    SELECT  @count = @count + 1
    PRINT @result
    END

Upvotes: 2

Views: 77

Answers (3)

Karthik Ganesan
Karthik Ganesan

Reputation: 4242

you have to initialize @result variable

Declare it like this

DECLARE @result nvarchar(10) = ''

this should work

Upvotes: 3

MDiesel
MDiesel

Reputation: 2667

It is returning a blank because you are concatenating the substring with @result, which initially is NULL.

Try setting @result to an empty string like this this:

SELECT  @string = '12345abc123', @result = ''

Upvotes: 3

Marc B
Marc B

Reputation: 360892

You never initialized @result, so it defaults to an sql null. SQL nulls are contagious poison, so when you do

SELECT @result = .... + @result

You're actually doing

SELECT @result = ... + null

and @result simply remains null

Initializing the value to an empty string solves your problem:

SET @result = ''

Upvotes: 8

Related Questions