0

i am concatenate string with int variable to string in sql for that i have write

Declare @convertname nvarchar(200) 
Declare @i int
Declare @iValueConvertToString  nvarchar(200)
Declare @tempSename nvarchar(200) 
Declare @checkname int; 

SET @i = 0

While(@i>=0)   
BEGIN
  @checkname=db0.fuctionabc (// this function check name is exist in record, if name is not exist it returns 0)  

  if(@checkname=0)
  begin
   --------- insert query ------------------
  Break;
  End
   SET @i=@i+1;

   SET @iValueConvertToString = CAST(@i AS varchar(10))

  SET @tempSename = @Convertname + '-'+ @iValueConvertToString

  SET @Convertname=@tempSename

END

this result me like

    abc-1
    abc-1-2
    abc-1-2-3
    abc-1-2-3-4

but i want result like 

abc-1
abc-2
abc-3
abc-4

what to change in my string concatenation logic?

i am new to sql please guide me.

vatsal
  • 262
  • 2
  • 13

3 Answers3

3

this will help you 

DECLARE @i  int = 1;
DECLARE @val nvarchar(99) = 'abc';
While(@i<= 5)
BEGIN
   PRINT @val + '-'+ cast(@i AS VARCHAR)
   SET @i=@i+1;
END
wiretext
  • 3,302
  • 14
  • 19
  • in your code but every time @convertname variable value is fixed as 'abc' but in my code after first iteration value convertname variable became 'abc-1' & than i use same as you logic it concate 2 at end & value became like 'abc-1- 2' rather than it has to be 'abc-2' – vatsal Feb 03 '16 at 06:15
  • i have updated my question ols see so you get better understand. – vatsal Feb 03 '16 at 06:21
  • but i am replace 'abc' with 'abc-1' so it result me wron concatation. – vatsal Feb 03 '16 at 06:34
  • @vatsal brother we don't need to replace then we will get your expected result – wiretext Feb 03 '16 at 06:35
  • i am checking this name is exist? if yes than concate - number & again check if no than insert value. else increment number & checking again – vatsal Feb 03 '16 at 06:36
  • @vatsal please update your Q in detail so all we get what you want because you didn't mention IF-ELSE :) – wiretext Feb 03 '16 at 06:38
2

Would something like this work for you?:

SET @i = 0
WHILE(@i <= 5)

BEGIN
    SET @i=@i+1;
    SET @iValueConvertToString = CAST(@i AS varchar(10))
    SET @output = @Convertname + '-'+ @iValueConvertToString
    PRINT @output;
END

You were intending to use @Convertname inside the WHILE loop as a constant, but you were concatenating it instead during each iteration. Using a separate variable for display purposes avoids this problem.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • in your code but every time @convertname variable value is fixed as 'abc' but in my code after first iteration value convertname variable became 'abc-1' & than i use same as you logic it concate 2 at end & value became like 'abc-1- 2' rather than it has to be 'abc-2' – vatsal Feb 03 '16 at 06:15
  • My understanding is that you _don't_ want the concatenation behavior you have. All the answers given should resolve this problem for you. – Tim Biegeleisen Feb 03 '16 at 06:17
  • i have updated my question ols see so you get better understand. – vatsal Feb 03 '16 at 06:21
0

You can do this without looping. Here is one method using a Tally Table:

DECLARE @convertname NVARCHAR(200) = 'abc'
DECLARE @limit INT = 5

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(@limit) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT *, @convertname + '-' + CAST(N AS VARCHAR(10)) 
FROM CteTally
Community
  • 1
  • 1
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67