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

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