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

Thanks, works like a charm....Don't know what I would do without this
list sometimes. 


PAUL HARTLAND
Database Designer/Programmer
paul.hartland at isharp.co.uk
ISHARP
DDI - 01922 472031
Mobile - 07730 523179

 

ISHARP (Information Services for Hospitality, Audit, Retail and
Pharmacy) provide IT resources for the Christie Group Stock & Inventory
Services companies.


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike &
Doris Manning
Sent: 29 June 2005 12:40
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


_______________________________________________

*  This message is confidential.
*  This email, its content and any files transmitted with it are intended solely for the addressee and may be legally privileged and/or confidential.
*  Access by any other party is unauthorised without the express written permission of the sender.
*  If you have received this email in error you may not copy or use the contents, attachments or information in any way and any review, use, dissemination, forwarding, disclosure, alteration, printing of this information is strictly prohibited. Please destroy it and notify the sender via return e-mail.
*  This email has been prepared using information believed by Paul Hartland to be reliable and accurate, but the company makes no warranty as to accuracy or completeness. In particular the author does not accept responsibility for changes made to this email after it was sent.
*  Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of the company or its affiliates.

The Orridge web site can be found at:
http://www.orridge.co.uk