[dba-SQLServer] Formatting A Number Into A Three Digit CharacterString

the RIGHT function assures that he will only be getting back the 3
digit data, because he is "ANDING" the 0's, then only selects to the
right of that assuring him he will always have 3 digits or characters

very clever indeed... 

On 6/29/05, dmcafee at pacbell.net <dmcafee at pacbell.net> wrote:
> Wow, that's much shorter than what I've been using:
> 
> REPLICATE('0', 3 - LEN(@intMin)) + CAST(@intMin AS VARCHAR(3))
> 
> or if substituting for NULLS:
> 
> REPLICATE('0', 3 - LEN(ISNULL(@intMin,'000'))) + CAST(ISNULL(@intMin,'000')
> AS VARCHAR(3))
> 
> 
> How did you come up with that? If I was to see that, I would assume that it
> would show up as 000999 (assuming @intMin = 999)
> 
> 
> David
> 
> 
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Mike &
> Doris Manning
> Sent: Wednesday, June 29, 2005 4:40 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Formatting A Number Into A Three Digit
> CharacterString
> 
> 
> Try
> 
> 'Level ' + RIGHT('000' + CAST(@intMin AS varchar(3)),3)
> 
> Doris Manning
> Database Administrator
> Hargrove Inc.
> www.hargroveinc.com
> 
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> paul.hartland at fsmail.net
> Sent: Wednesday, June 29, 2005 3:26 AM
> To: SQL Group
> Subject: [dba-SQLServer] Formatting A Number Into A Three Digit Character
> String
> 
> 
> To all,
> 
> I have the foloowing stored procedure :
> 
> CREATE PROCEDURE [genesis_insert_ResourceGuide]
>  (
>   @strClient  [nvarchar](255),
>   @strJobType  [nvarchar](100),
>   @strACNo  [nvarchar](50),
>   @strLevel  [nvarchar](75),
>   @intMin   [int],
>   @intMax  [int]
>         )
> AS
> SET NOCOUNT ON
> WHILE    @intMin  <= @intMax
> BEGIN
> INSERT INTO   tblResourceGuide
>   (
>    [Name],
>    [ClientJobType],
>    [A/CNo],
>    [CountLevel],
>    [Items],
>    [Value],
>    [Manager],
>    [Supervisor],
>    [Control],
>    [Checker],
>    [Counter],
>    [Planner],
>    [Dispensary],
>    [Pharmacy]
>      )
> VALUES
>   (
>    @strClient,
>    @strJobType,
>    @strACNo,
>    'Level ' + CONVERT(nvarchar, at intMin),
>    0,
>    0,
>    0,
>    0,
>    0,
>    0,
>    0,
>    0,
>    0,
>    0
>      )
> SET   @intMin  = @intMin  + 1
> IF    @intMin  <= @intMax
>  CONTINUE
> ELSE
>  BREAK
> END
> SET NOCOUNT OFF
> GO
> 
> This line 'Level ' + CONVERT(nvarchar, at intMin) as you will see converts a
> number into a string and adds it to Level (i.e Level 1, Level 2 etc
> etc).....The maximum a level will ever be is 999, so what I would like
> instead of Level 1, Level 2 etc etc is Level 001, Level 002 etc etc.  Can
> someone tell me the function or code that I can use to achieve this.
> 
> Thanks in advance for any help on this...
> 
> Paul Hartland
> Database Developer
> 
> --
> 
> Whatever you Wanadoo:
> http://www.wanadoo.co.uk/time/
> 
> This email has been checked for most known viruses - find out more at:
> http://www.wanadoo.co.uk/help/id/7098.htm
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 


-- 
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...