How to add varchar and integer in sql server

Question

I have to convert int to varchar and need to take care about one more consideration.

For example:

1)My string length is always 10 characters. (i.e. "0000000001")

Whenever I generated a new id, it has to increment (i.e. "0000000002")
When it reaches the 10th string will be "0000000010", etc...
I have no idea how to implement this. trying as a first step.

--Declared variable to increment count
declare @CTR INT
--@LIPRequestID is integer this is what i have to add to my 
declare @LIPRequestID int
select @LIPRequestID=0
declare @LIPRequestIDstring varchar(max)
select @ctr=0
WHILE @CTR<2
BEGIN
select @ctr=@ctr+1
select @LIPRequestID=@LIPRequestID+1

select @LIPRequestIDstring='00000000'+ CAST(@LIPRequestID AS VARCHAR(10)
print @LIPRequestIDstring
END

but it is throwing the following exception error:

Msg 102, Level 15, State 1, Line 14 Incorrect syntax near '@LIPRequestIDstring'.

can anybody suggest where I am going wrong?

possible duplicate of [How do I convert an int to a zero padded string in T-SQL?](http://stackoverflow.com/questions/309165/how-do-i-convert-an-int-to-a-zero-padded-string-in-t-sql) — Tab Alleman, Feb 19 '15 at 14:11
Why do you need a string that sort of behaves like a number? Why not just use a number and forget the padded 0's? — Sean Lange, Feb 19 '15 at 14:32

score 0 · Answer 1 · answered Feb 19 '15 at 13:13

0

Parenthesis missing in cast function, other things looks fine

select @LIPRequestIDstring='00000000'+ CAST(@LIPRequestID AS VARCHAR(10))--here

answered Feb 19 '15 at 13:13

Pரதீப்

91,748
19
131
172

Tim Schmelter · Accepted Answer · 2015-02-19T13:26:02.917

0

Instead of concatenating the string literal 00000000 with the ID you should use RIGHT:

DECLARE @LIPRequestIDstring varchar(10);
DECLARE @nextLIPRequestID int;
SET @nextLIPRequestID = (SELECT MAX(LIPRequestID) FROM dbo.TableName) + 1;
SET @LIPRequestIDstring = RIGHT('0000000000'+ CONVERT(VARCHAR(10),@nextLIPRequestID),10)

edited Feb 19 '15 at 13:26

answered Feb 19 '15 at 13:17

Tim Schmelter

450,073
74
686
939

The script will not work for a single digit integer. The last line of the script should have 9 zeros instead of 8 `SET @LIPRequestIDstring = RIGHT('000000000'+ CONVERT(VARCHAR(10),@nextLIPRequestID),10)` Apart from that the script works like a charm. – Pankaj Feb 19 '15 at 13:25
there are no tables yet, number should genarate on the fly and need to store this in the table – Chaitanya Phani Feb 19 '15 at 13:26
@Pankaj: i guess 10 digits instead of 8 because "always my string length is 10 characters". – Tim Schmelter Feb 19 '15 at 13:27
@chaithanyakishore: how do you want to calculate the next number if you don't store the last? Or do you want to extract it from the last/maximum number? You have to use `CONVERT` to get an `int` from the varchar number like here: `CONVERT(int, (SELECT MAX(LIPRequestID) FROM dbo.TableName))`. Then add `1` and you have the new number. – Tim Schmelter Feb 19 '15 at 13:28
@Tim Schmelter: Actually i have to create dummy data into the tables, for that there is a coloumn with auto genarated and it will save. I am trying to create this value and have to use this as reference key in other tables – Chaitanya Phani Feb 19 '15 at 13:31

score 0 · Answer 3 · answered Feb 19 '15 at 13:17

0

Instead of

CAST(@LIPRequestID AS VARCHAR(10)

use

CAST(@LIPRequestID AS VARCHAR(10))

answered Feb 19 '15 at 13:17

Imran Ali Khan

8,469
16
52
77

yes,correct this silly mistake, but 2nd issue, but while incrementing the number to 10 my string is becoming 11 characters, if string length is more than 10 characters have to remove the first '0'. please suggest – Chaitanya Phani Feb 19 '15 at 13:24

score 0 · Answer 4 · answered Feb 19 '15 at 16:40

Instead of RIGHT function, we can use REPLACE function. This will help you to implement your two requirements. The length of the ID also can be passed as parameter @IDLength.

DECLARE @CTR INT = 0
DECLARE @LIPRequestID INT = 0

DECLARE @IDLength INT = 8

DECLARE @LIPRequestIDstring VARCHAR(MAX)

WHILE (@CTR < 12)
    BEGIN
        SELECT @CTR += 1
        SELECT @LIPRequestID += 1
        PRINT  REPLACE(STR(@LIPRequestID, @IDLength), SPACE(1), '0')
    END

score 0 · Answer 5 · answered Feb 20 '15 at 05:59

0

Yes, Right function really make sence, thanks to sql and stackoverflow.

declare @LIPRequestIDstring varchar(max) select @LIPRequestID=@LIPRequestID+1 select @LIPRequestIDstring=RIGHT('0000000000'+ CONVERT(VARCHAR(10),@LIPRequestID),10)

answered Feb 20 '15 at 05:59

Chaitanya Phani

127
1
2
12

How to add varchar and integer in sql server

5 Answers5