November 15, 2017 at 8:38 pm
I am trying to create a text variable called @varnumber with a leading zero for values less that 10. Below is my code and the result from my code is only the leading digit: I do not see the whole number. I have tried 3 different ways (two are commented out that didn't work as well) What am I doing wrong? Thanks
Code:
DECLARE @Number as int = 0
DECLARE @varnumber as varchar
WHILE @Number < = 79
BEGIN
SET @Number = @Number + 1
--Set @varnumber =RIGHT ('00'+ CAST (@Number AS varchar), 2)
--Set @varnumber =RIGHT('00' + CONVERT(varchar,@number), 2)
Set @varnumber=RIGHT('00' + CONVERT(varchar(2), @number), 2)
print @varnumber
end
Result:
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
..
7
7
7
8
November 15, 2017 at 10:19 pm
Jocar26 - Wednesday, November 15, 2017 8:38 PMI am trying to create a text variable called @varnumber with a leading zero for values less that 10. Below is my code and the result from my code is only the leading digit: I do not see the whole number. I have tried 3 different ways (two are commented out that didn't work as well) What am I doing wrong? ThanksCode:
DECLARE @Number as int = 0
DECLARE @varnumber as varcharWHILE @Number < = 79
BEGIN
SET @Number = @Number + 1
--Set @varnumber =RIGHT ('00'+ CAST (@Number AS varchar), 2)
--Set @varnumber =RIGHT('00' + CONVERT(varchar,@number), 2)
Set @varnumber=RIGHT('00' + CONVERT(varchar(2), @number), 2)print @varnumber
endResult:
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
..
7
7
7
8
You're problem is that you allowed a default to bite you. See the following code for what I mean.
DECLARE @Number as int = 0
DECLARE @varnumber as varchar(2) -- <----<<<<< Look HERE!
WHILE @Number < = 79
BEGIN
SET @Number = @Number + 1
Set @varnumber=RIGHT('00' + CONVERT(varchar(2), @number), 2)
print @varnumber
end
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2017 at 6:09 am
Hi Jeff, thank you very much!!! It's always those little things isn't it. I looked for declare varchar examples and missed the right one. 😎
November 16, 2017 at 6:53 am
And here's a small improvement.
DECLARE @Number as int = 0
DECLARE @varnumber as varchar(2)
WHILE @Number < = 79
BEGIN
SET @Number = @Number + 1
Set @varnumber= RIGHT( 100 + @number, 2)
print @varnumber
end
November 16, 2017 at 7:50 am
Thanks Luis. I am an advance beginner, so please explain the improvement 8-).
(Updated) Is it just fewer characters? Thanks
November 16, 2017 at 7:59 am
Jocar26 - Thursday, November 16, 2017 7:50 AMThanks Luis. I am an advance beginner, so please explain the improvement 8-).
(Updated) Is it just fewer characters? Thanks
It's real simple. If you take, for example, the number "7" and add "100" to it, you get "107". If you take the RIGHT 2 of that, you get "07". The reason why it can be an improvement is that there's no string concatenation and most of the work is done by Integer Math instead. It also doesn't require any VARCHAR variables, which carry and extra 2 bytes that define the length of the VARCHAR.
Since you are a newbie willing to learn, I'll also tell you that using a While Loop or Recursive CTE cripples SQL Server when it comes to counting. Please see the following article for an introduction to what is known as a Numbers or Tally Table and DO understand that it's only an introduction providing the proverbial tip of the iceberg. It explains the principle of every SELECT actually being a loop behind the scenes that a couple of us call a "pseudo cursor"... and done correctly, they're nasty fast.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2017 at 8:03 am
Thanks to both of you, Luis and Jeff!!!
November 16, 2017 at 10:47 am
I certainly agree that the overhead of a variable isn't needed.
But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY number
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2017 at 11:30 am
ScottPletcher - Thursday, November 16, 2017 10:47 AMI certainly agree that the overhead of a variable isn't needed.But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY number
But, if you're going that route, why bother casting at all?
;
WITH cteTally10 AS (
SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) AS numbers(number)
),
cteTally100 AS (
SELECT c10.number + c1.number AS num, ROW_NUMBER() OVER(ORDER BY c10.number, c1.number) - 1 AS rn
FROM cteTally10 c10
CROSS JOIN cteTally10 c1
)
SELECT num
FROM cteTally100
WHERE rn BETWEEN 1 AND 79
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2017 at 11:41 am
drew.allen - Thursday, November 16, 2017 11:30 AMScottPletcher - Thursday, November 16, 2017 10:47 AMI certainly agree that the overhead of a variable isn't needed.But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY numberBut, if you're going that route, why bother casting at all?
;WITH cteTally10 AS (
SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) AS numbers(number)
),
cteTally100 AS (
SELECT c10.number + c1.number AS num, ROW_NUMBER() OVER(ORDER BY c10.number, c1.number) - 1 AS rn
FROM cteTally10 c10
CROSS JOIN cteTally10 c1
)
SELECT num
FROM cteTally100
WHERE rn BETWEEN 1 AND 79
;Drew
For consistency, aka for ease of use and maintainability. A tally table is numeric by definition. I won't corrupt that model for a simple task like this. For simple tasks, use the most straightforward, consistent approach. There's no need to be "cute" here.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2017 at 12:04 pm
ScottPletcher - Thursday, November 16, 2017 11:41 AMdrew.allen - Thursday, November 16, 2017 11:30 AMScottPletcher - Thursday, November 16, 2017 10:47 AMI certainly agree that the overhead of a variable isn't needed.But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY numberBut, if you're going that route, why bother casting at all?
;WITH cteTally10 AS (
SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) AS numbers(number)
),
cteTally100 AS (
SELECT c10.number + c1.number AS num, ROW_NUMBER() OVER(ORDER BY c10.number, c1.number) - 1 AS rn
FROM cteTally10 c10
CROSS JOIN cteTally10 c1
)
SELECT num
FROM cteTally100
WHERE rn BETWEEN 1 AND 79
;Drew
For consistency, aka for ease of use and maintainability. A tally table is numeric by definition. I won't corrupt that model for a simple task like this. For simple tasks, use the most straightforward, consistent approach. There's no need to be "cute" here.
I have to respectfully disagree. First, this IS a tally table. The rn returned by the CTE is just as numeric as the one returned by your version of the tally table. Mine just has an extra column for the pre-conversion to a string. This is no different from creating a calendar table with specialized fields to handle special situations.
Also, this approach is both straightforward and consistent. It is not an attempt to be "cute." It may very well be that pre-converting the numbers performs much better, but my tests on such a small sample didn't produce reliable enough results to base a decision on. The differences in duration seemed to be affected much more by the state of the server than any difference in the actual approaches.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2017 at 4:34 pm
drew.allen - Thursday, November 16, 2017 11:30 AMScottPletcher - Thursday, November 16, 2017 10:47 AMI certainly agree that the overhead of a variable isn't needed.But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY numberBut, if you're going that route, why bother casting at all?
;WITH cteTally10 AS (
SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) AS numbers(number)
),
cteTally100 AS (
SELECT c10.number + c1.number AS num, ROW_NUMBER() OVER(ORDER BY c10.number, c1.number) - 1 AS rn
FROM cteTally10 c10
CROSS JOIN cteTally10 c1
)
SELECT num
FROM cteTally100
WHERE rn BETWEEN 1 AND 79
;Drew
Hmmmm.... I wonder what the performance difference is between that "always overshoot" method and the cast method when you have a slew of low values to convert. For such a low row count it may not ever make a hill of beans but if someone tries to use that same method for values that could measure in the thousands instead just 2 digit values, the performance will certainly tank for the overshoot.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2017 at 4:41 pm
ScottPletcher - Thursday, November 16, 2017 11:41 AMdrew.allen - Thursday, November 16, 2017 11:30 AMScottPletcher - Thursday, November 16, 2017 10:47 AMI certainly agree that the overhead of a variable isn't needed.But tThe RIGHT function is going to implicitly convert the value to varchar anyway, so I would stick with doing this in a straightforward way, i.e. a "standard" in-line tally table and normal CASTing:
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT CASE WHEN number < 10 THEN '0' ELSE '' END + CAST(number AS varchar(2))
FROM cteTally100
WHERE number <=79
ORDER BY numberBut, if you're going that route, why bother casting at all?
;WITH cteTally10 AS (
SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) AS numbers(number)
),
cteTally100 AS (
SELECT c10.number + c1.number AS num, ROW_NUMBER() OVER(ORDER BY c10.number, c1.number) - 1 AS rn
FROM cteTally10 c10
CROSS JOIN cteTally10 c1
)
SELECT num
FROM cteTally100
WHERE rn BETWEEN 1 AND 79
;Drew
For consistency, aka for ease of use and maintainability. A tally table is numeric by definition. I won't corrupt that model for a simple task like this. For simple tasks, use the most straightforward, consistent approach. There's no need to be "cute" here.
I agree, especially since this particular form of "cuteness" requires a fixed overshoot of calculating 100 strings even though only 1 may be required.
As a bit of a sidebar, the "cuteness" also requires concatenation of strings, which is surprisingly expensive at times. I've see times where, although is made the code simpler, was a whole lot more expensive than virtually any other type of string functionality in the same code. XML string splitters that doe such concatenation are a great example of such performance problems.
It does, however, still contain "Tally Table-Like" functionality. If a TOP clause were included to limit the return of the second CTE, it might be a contender even though it's a bit different from the norm. Sometimes you need to do something different to get that extra bit of speed out of things. If you think someone might not get it because of the odd form, the addition of a simple and thoughtful comment will remove all doubt.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply