June 29, 2015 at 6:18 pm
Comments posted to this topic are about the item Conver Number to string ( exp. 9 - 0009 or 9 - 09)
July 2, 2015 at 10:14 am
How about
RIGHT(REPLICATE('0',@size) + CAST(@value as varchar(max)),@size)
July 21, 2015 at 9:09 pm
mak101 (7/2/2015)
How aboutRIGHT(REPLICATE('0',@size) + CAST(@value as varchar(max)),@size)
I was thinking the same thing (maybe not varchar(max) though ;-))
-- Itzik Ben-Gan 2001
July 22, 2015 at 1:24 am
Wouldn't it be better to use something like this:
SELECT RIGHT(REPLACE(STR(@NUM),' ','0'),5)
Reference:
July 22, 2015 at 3:30 am
Or
[font="Courier New"]REPLACE(STR(@NUM, 5),' ','0')[/font]
July 22, 2015 at 9:26 am
Agree with using REPLICATE instead, and get rid of any extraneous local variables, so fully coded would look like this:
CREATE FUNCTION [dbo].[NumberToString] (
@value int,
@size int
)
RETURNS varchar(20)
AS
BEGIN
RETURN (
SELECT RIGHT(REPLICATE('0', @size) + CAST(@value AS varchar(20)), @size)
)
END
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 22, 2015 at 11:43 am
Good solution Mak - clean, easy to inline so no udf performance hit - interesting if there are any counter points/other ideas
Tom in Sacramento For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2015 at 11:58 am
Again I wonder how they choose a "Featured Script". Is this "featured" as an anti-pattern? I guess that I assume something is featured to show a best practice. Not worst.
A loop adding zeroes to the front might not be the absolute worst way. I suppose one could concoct something worse. But this is pretty bad.
Then there is the lack of comments and this:
DECLARE @Tmp VARCHAR (128);
SET @Tmp = CAST(@value AS VARCHAR(20));
An INT can go from -2147483648 to 2147483647. 10+1 characters. No guard for negatives, so call it 10. But why put a length at all? CAST(@value as VARCHAR). If one were to use this technique at all that is.
Assuming that passing in a NULL for @value should result in a string of zeroes is at least worthy of a comment? Yes? IMHO the behavior would be more consistent with the rest of SQL if NULL passed in for @value would return NULL or an error. NULL operation anything s/b NULL.
If I wanted to get real nit-picky I'd even ask why int is lower case for one parameter-type and INT is upper case for the other. But that is straying into obsessive compulsive grammar Nazi territory. Even if it is a good idea for a best practice to be consistent.
For SQL 2012 and later I would suggest: RETURN FORMAT(@value,REPLICATE('0',@size)). It will fail for @value=NULL but the rest of the cruft just goes away.
July 22, 2015 at 1:45 pm
gshouse (7/22/2015)
If I wanted to get real nit-picky I'd even ask why int is lower case for one parameter-type and INT is upper case for the other. But that is straying into obsessive compulsive grammar Nazi territory. Even if it is a good idea for a best practice to be consistent.
Actually not nit-picky. You should always use lower case, because there are times SQL Server errors out when trying to process upper case data type names!
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 28, 2015 at 7:44 am
Well clearly there are a variety of ways to do this.
August 7, 2015 at 7:01 am
There must be a few ways to do this. Which would be quicker though...
The original article's loop or this:
"SELECT RIGHT(REPLICATE('0', @size) + CAST(@value AS varchar(20)), @size)"
I'm guessing the latter would run circles around a loop, specially for millions of rows.
Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply