Help with creating a string

• Sql Student-446896

  Default port

  Points: 1425

  More actions

  January 25, 2013 at 10:31 am

  #273415

  Hi,

  I have a beginning Year and a End year and i have to compute/create a string based on the given years.

  Example:

  Input: BegYr = 2013 and EndYr = 2015

  Output: CombYr = 3/4/5

  How do i do this as script?

• sqlbi.vvamsi

  Ten Centuries

  Points: 1194

  More actions

  January 25, 2013 at 11:16 am

  #1581158

  this should work

  declare @start int=2013,@end int=2015

  ;with cte(temp,yr)

  as

  (

  select @start,cast(RIGHT(@start,1) as varchar)

  union all

  select temp+1,cast(yr+'/'+RIGHT(temp+1,1) as varchar) FROM CTE WHERE temp<@end

  ) SELECT max(yr) as result FROM cte

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 25, 2013 at 12:19 pm

  #1581188

  Assuming you have a Tally table you can try this:

  DECLARE @Start_year int, @End_Year int;

  SET @Start_Year = 2011 ;

  SET @End_Year = 2013;

  SELECT STUFF((

  SELECT '/' + RIGHT( CAST( N AS varchar(4)), 1)

  FROM Tally

  WHERE N BETWEEN @Start_Year AND @End_Year

  ORDER BY N

  FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'');

  If you don't know what a Tally table is, then search for it on this site ;-).

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 25, 2013 at 1:14 pm

  #1581205

  Please don't cross post or create multiple threads for the same issue. It just fragments your responses.

  Please direct further replies here. http://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/