Create string using input values

Question

Create string using input values

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

db4breakfast Ten Centuries Points: 1097 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)