Conver Number to string ( exp. 9 - 0009 or 9 - 09)

Question

Post reply

Conver Number to string ( exp. 9 - 0009 or 9 - 09)

dnovelov

SSC Rookie

Points: 30
More actions
June 29, 2015 at 6:18 pm

#299528

Comments posted to this topic are about the item Conver Number to string ( exp. 9 - 0009 or 9 - 09)

Viewing 11 posts - 1 through 10 (of 10 total)

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

mak101 Ten Centuries Points: 1111 More actions · Answer 1

How about

RIGHT(REPLICATE('0',@size) + CAST(@value as varchar(max)),@size)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

mak101 (7/2/2015)
How about
RIGHT(REPLICATE('0',@size) + CAST(@value as varchar(max)),@size)

I was thinking the same thing (maybe not varchar(max) though ;-))

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Tathagat Verma SSC Journeyman Points: 94 More actions · Answer 3

Wouldn't it be better to use something like this:

SELECT RIGHT(REPLACE(STR(@NUM),' ','0'),5)

Reference:

http://stackoverflow.com/a/779142/245764

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 4

Or

[font="Courier New"]REPLACE(STR(@NUM, 5),' ','0')[/font]

ScottPletcher SSC Guru Points: 101078 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Tom_Sacramento SSCertifiable Points: 6288 More actions · Answer 6

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/

gshouse Say Hey Kid Points: 664 More actions · Answer 7

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.

ScottPletcher SSC Guru Points: 101078 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

Well clearly there are a variety of ways to do this.

Michael G Ten Centuries Points: 1323 More actions · Answer 10

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. 😀