Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create string using input values Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 12:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
Sql Student-446896 (1/25/2013)
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?

Just double checking... What do you want for a start year of 2009 and and end year of 2021? Read this carefully.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412205
Posted Monday, January 28, 2013 9:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
OK, OK, I surrender. But for creating sample data I see no reason not to use a loop--it's quick even if dirty. I was playing around and spent enough time as it was so just went with a loop for building the strings. Sorry. (Backs away with tail between legs...)

 
Post #1412491
Posted Monday, January 28, 2013 5:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
Steven Willis (1/28/2013)
OK, OK, I surrender. But for creating sample data I see no reason not to use a loop--it's quick even if dirty. I was playing around and spent enough time as it was so just went with a loop for building the strings. Sorry. (Backs away with tail between legs...)

 


BWAA-HAAA!!!! Understood! The only reasons I can offer you to not use a loop to build sample data is that you're NOT practicing using set based code when you write the loop and because it'll take a relatively painful amount of time to run if you ever need to test with a substantial number of rows.

On the main problem, I was (and still am) totally amazed and even a little disgusted that, even after Lynn posted a set based solution, people were still posting While loop solutions. A couple even justified it with the ol' "it's slow but it works" excuse.

Just say "NO" to the mind drug known as "loops" in T-SQL.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412705
Posted Monday, January 28, 2013 6:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 3,618, Visits: 5,254
Jeff Moden (1/28/2013)

Just say "NO" to the mind drug known as "loops" in T-SQL.


I do!!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1412719
Posted Monday, January 28, 2013 7:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
dwain.c (1/28/2013)
Jeff Moden (1/28/2013)

Just say "NO" to the mind drug known as "loops" in T-SQL.


I do!!




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412723
Posted Sunday, February 3, 2013 2:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:55 AM
Points: 386, Visits: 363
Jeff Moden (1/28/2013)
C'mon, folks! Just look at the mess of code that a While loop makes for this! No one should be writing a While Loop for this!


Hi Jeff,

I'm inspired by your anti-RBAR philosophy, I went back and come up with a 2nd solution with no loop. : )
Anyway, the reason I used the while loop because I assumed there's no "sequence" table.
--generate a sequence of digit from 0 to n
CREATE TABLE DBO.NUMBERS(i INT NOT NULL PRIMARY KEY);
go
INSERT INTO dbo.numbers(i)VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
go

INSERT INTO dbo.numbers(i)
SELECT num
from (
SELECT (n4.i * 1000 + n3.i * 100 + n2.i * 10 + n1.i) AS num
FROM dbo.numbers n1
CROSS JOIN dbo.numbers n2
CROSS JOIN dbo.numbers n3
CROSS JOIN dbo.numbers n4
) x
WHERE num > 9
ORDER BY num;

--goal: Output CombYr = 3/4/5/
--Input: @startYr int, @endYr int
--assume inputs are valid. and year is 4-digit format. and EndYr - BegYr <= 10
DECLARE @startYr INT = 2011
, @endYr INT = 2013;
DECLARE @outputStr NVARCHAR(100) = '';

SELECT @outputStr = @outputStr + RIGHT(CAST(n.i AS VARCHAR(4)), 1) + '/'
FROM dbo.numbers n
WHERE n.i BETWEEN @startYr AND @endYr;

SELECT @outputStr;



--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Post #1415059
Posted Sunday, February 3, 2013 3:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
tnk7200 (2/3/2013)
Jeff Moden (1/28/2013)
C'mon, folks! Just look at the mess of code that a While loop makes for this! No one should be writing a While Loop for this!


Hi Jeff,

I'm inspired by your anti-RBAR philosophy, I went back and come up with a 2nd solution with no loop. : )
Anyway, the reason I used the while loop because I assumed there's no "sequence" table.
--generate a sequence of digit from 0 to n
CREATE TABLE DBO.NUMBERS(i INT NOT NULL PRIMARY KEY);
go
INSERT INTO dbo.numbers(i)VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
go

INSERT INTO dbo.numbers(i)
SELECT num
from (
SELECT (n4.i * 1000 + n3.i * 100 + n2.i * 10 + n1.i) AS num
FROM dbo.numbers n1
CROSS JOIN dbo.numbers n2
CROSS JOIN dbo.numbers n3
CROSS JOIN dbo.numbers n4
) x
WHERE num > 9
ORDER BY num;

--goal: Output CombYr = 3/4/5/
--Input: @startYr int, @endYr int
--assume inputs are valid. and year is 4-digit format. and EndYr - BegYr <= 10
DECLARE @startYr INT = 2011
, @endYr INT = 2013;
DECLARE @outputStr NVARCHAR(100) = '';

SELECT @outputStr = @outputStr + RIGHT(CAST(n.i AS VARCHAR(4)), 1) + '/'
FROM dbo.numbers n
WHERE n.i BETWEEN @startYr AND @endYr;

SELECT @outputStr;



Good start! Now, make it run for a whole table of Start and End dates and you'll really be on your way to avoiding RBAR. The use of any Scalar Function or Multi-Line TVF will be a disqualifier because those are a hidden form of RBAR. Truth is, no function is actually required here. Some of the scripts with the correct answer in this thread look a bit complicted because they decided to build the Tally structure on the fly but go have a look at those.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415062
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse