Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server concatenation varchar and int

Tags:

sql

sql-server

I have two variables:

One is varchar and one is int, I am not sure how to write a while loop using casting so that it will show the following: for example if the while loop is 5 then the result should be Meter 1 Meter 2 Meter 3 Meter 4 Meter 5.

I have this code, but it is not running (cannot convert varchar to int) even when i do the casting it does not work.

DECLARE @Name varchar (20) = 'Meter',
        @MeterNumber int = 1

WHILE (@MeterNumber < 5)
BEGIN
PRINT @Name + ' ' + @MeterNumber
SET  @MeterNumber = @MeterNumber + 1
END
like image 856
Shatha S Avatar asked Jul 26 '13 22:07

Shatha S


2 Answers

This should do the trick: cast the MeterNumber to a varchar when you print it.

DECLARE @Name varchar (20) = 'Meter',
        @MeterNumber int = 1

WHILE (@MeterNumber <= 5)
BEGIN
PRINT @Name + ' ' + cast(@MeterNumber as varchar(20))
SET  @MeterNumber = @MeterNumber + 1
END

EDIT:

For example I want to declare a third variable that would store each iteration. Meter 1, Meter 2... but I am not sure where to place it!

DECLARE @Name varchar (20) = 'Meter',
        @MeterNumber int = 1 ,
        @OutPut varchar(max) =''; -- this can get biiiig.

WHILE (@MeterNumber <= 5)
BEGIN
SET @Output = @Output + @Name + ' ' + cast(@MeterNumber as varchar(20)) + ','
PRINT LEFT(@Output, len(@Output) - 1)
SET  @MeterNumber = @MeterNumber + 1
END
like image 113
Fabian Bigler Avatar answered Oct 06 '22 04:10

Fabian Bigler


Try this one (without LOOP) -

DECLARE
      @Name VARCHAR(20) = 'Meter'
    , @MeterNumber INT = 5
    , @OutPut VARCHAR(MAX) = ''

SELECT @OutPut = STUFF((
     SELECT CHAR(13) + @Name + ' ' + CAST(sv.number AS VARCHAR(5)) 
     FROM [master].dbo.spt_values sv
     WHERE sv.[type] = 'p'
     AND sv.number BETWEEN 1 AND @MeterNumber
     FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')

PRINT @OutPut
like image 31
Devart Avatar answered Oct 06 '22 03:10

Devart