SQL Server Add integer variable to end of string

Question

I want to generate some integers in SQL Server loop and add it to end of string that is a long number

Declare @i int
Set @i = 10

While @i < 45
Begin
    insert into AllowedStudents (GroupID, StudentNumber) 
    values ('1', '9210410' + @i)

    SET @i = @i + 1
End

but when insert process ends I see this result :

28240   1   9210420
28241   1   9210421
28242   1   9210422
28243   1   9210423

against

28240   1   921041020
28241   1   921041021
28242   1   921041022
28243   1   921041023

marc_s · Accepted Answer · 2015-11-07 09:53:21Z

3

Convert @i from int to varchar

Like this

insert into AllowedStudents (GroupID, StudentNumber) 
values ('1', '9210410' + Convert(varchar(50), @i))

SQLFIDDLE

edited Nov 7, 2015 at 9:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 7, 2015 at 9:02

Anik Islam Abhi

25.4k8 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2015-11-07 09:53:07Z

0

This is because of type precedence in SQL Server. Int datatype has higher precedence than varchar datatype, causing varchar to be implicitly converted to int type. You need to explicitly convert your int variable to varchar datatype.

This can be done with cast or convert functions:

'9210410' + cast(@i as varchar(100))

or

'9210410' + convert(varchar(100), @i)

For more information on datatype precedence visit https://msdn.microsoft.com/en-us/library/ms190309.aspx

edited Nov 7, 2015 at 9:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 7, 2015 at 9:33

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

SQL Server Add integer variable to end of string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related