December 6, 2004 at 2:05 pm
I have an integer field that I want to convert and left pad the field with "0"s to make the field a total of 5 characters. Any suggestions? I can not find a left pad function.
December 6, 2004 at 2:10 pm
Select right('00000' + cast(MyFieldName as varchar(5)), 5) as MyFieldName from MyTable
December 6, 2004 at 2:12 pm
There is no Left pad function as this can be considered a presentational issue you have Try this:
DECLARE @MeineZahl INT
SET @MeineZahl = 99
SELECT
REPLACE(STR(@MeineZahl,10), ' ', '0')
Rechtsbündig
----------------------
0000000099
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 7, 2004 at 5:28 am
DECLARE @intX int
SET @intX = 123
SELECT RIGHT(CONVERT(varchar,1000000 + @intX),5)
------
00123
December 7, 2004 at 6:34 am
Here is a User Defined Function that can be called to perform this functionality wherever and whenever needed. Best of luck!
-- ************************************************************************************
-- Create Function
-- ************************************************************************************
CREATE FUNCTION tsgudf_FormatPadLeft
(@StringToPad VARCHAR (255) = NULL,
@PadChar CHAR (1) = '0',
@TotalLength INT = 8)
RETURNS VARCHAR (255)
AS
/* ***********************************************************************************
** Domain: TSGJAX.COM
** Server: Generic
** Database: Generic
** Project: Formatting Scalar Functions
**
** Name: tsgudf_FormatPadLeft
** Decription: This scalar function returns a string padded with any
** specified character (e.g. 0 or blank) to a specified
** lenght including both the padded and original values.
** Author Name: Joe Salvatore
** Author Title: Programmer/Analyst
** Author Company: The Stellar Group
** Author Phone: XXX-XXX-XXXX
** Author Email: jsalvatore@thestellargroup.com
**
** Scheduling: Not Applicable - Callable Scalar Function
**
**************************************************************************************
** CHANGE HISTORY
** Change No: Date: Author: Description:
** _________ ___________ _______________ ____________________________________
** 001 12/31/2003 Joe Salvatore Created.
**
************************************************************************************ */
-- ************************************************************************************
-- Example To Execute the Function
-- ************************************************************************************
-- SELECT
-- dbo.tsgudf_FormatPadLeft(LTRIM(STR(123)),'0',8) AS FormatedResult,
-- LEN(RTRIM(LTRIM(STR(123)))) AS Length,
-- 8-LEN(RTRIM(LTRIM(STR(123)))) AS LengthToPad
-- Returns FormatedResult = 00000123 Length = 3 and LengthToPad = 5
BEGIN
-- ************************************************************************************
-- Declare and Set Local Variables
-- ************************************************************************************
DECLARE
@Error INTEGER, -- Local variable to capture the error code.
@ErrMsg NVARCHAR(1000), -- Error message to return
@Parameters NVARCHAR(4000), -- String representing parameters
@PaddedResult VARCHAR (255)
-- ************************************************************************************
-- Set Parameter String to be Used for Error Handling
-- ************************************************************************************
SET @Parameters = ''
+ ' @StringToPad:'
+ COALESCE(CONVERT(VARCHAR, @StringToPad), 'NULL')
+ ' @PadChar:'
+ COALESCE(CONVERT(VARCHAR, @PadChar), 'NULL')
+ ' @TotalLength:'
+ COALESCE(CONVERT(VARCHAR, @TotalLength), 'NULL')
SET @Error = 0
-- ************************************************************************************
-- Main Function Logic
-- ************************************************************************************
BEGIN
-- Length of Input String is a less than desired (padding needed)
IF @TotalLength > LEN(RTRIM(LTRIM(@StringToPad)))
SET @PaddedResult =
REPLICATE(@PadChar,@TotalLength-LEN(LTRIM(RTRIM(@StringToPad))))
+ @StringToPad
-- Length of Input String is the same or greater length then desired (no padding needed)
IF @TotalLength <= LEN(RTRIM(LTRIM(@StringToPad)))
SET @PaddedResult =
(LTRIM(RTRIM(@StringToPad)))
END
-- ************************************************************************************
-- Error Handling
-- ************************************************************************************
SELECT @Error = @@Error
IF (@Error != 0)
BEGIN
SELECT @ErrMsg = 'Server: Msg 60001, Level 16, State 2, Line 1 ' +
CHAR(13) + CHAR(10) + 'Error using parameters ' +
+ @Parameters +
' provided to dbo.tsgudf_FormatPadLeft.'
GOTO ENDERROR
END
GOTO ENDOK
-- ************************************************************************************
-- Set Function Return Value
-- ************************************************************************************
ENDERROR:
BEGIN
RETURN @StringToPad
END
ENDOK:
RETURN @PaddedResult
END
December 7, 2004 at 7:29 am
This will pad out any column or variable to what ever length you need.
CONVERT(varchar(5), REPLICATE('0', 5 - DATALENGTH(CONVERT(varchar(5), ColA))) + CONVERT(varchar(5), ColA))
December 7, 2004 at 7:36 pm
And to bum Chad's a little more:
reverse(cast(reverse(REPLICATE('0', 5) + @value) as varchar(5)))
Signature is NULL
December 8, 2004 at 12:50 am
IMHO, there is neither a need for a scalar UDF (a real performance killer on larger tables) nor for a tour de force in casting, converting, reversing.... If you don't want to do this at the client (where it really belongs to), see Remi's or my reply above.
Btw, Calvin:
declare @ColA int
set @ColA = 99
select
CONVERT(varchar(5)
, REPLICATE('0', 5 - DATALENGTH(CONVERT(varchar(5), @ColA))) +
CONVERT(varchar(5), @ColA))
select
reverse(cast(reverse(REPLICATE('0', 5) + @ColA) as varchar(5)))
-----
00099
(1 row(s) affected)
-----
99
(1 row(s) affected)
What am I doing wrong that I don't get the desired result with your suggestion?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 8, 2004 at 2:02 am
Surely the fewer text functions the better? Frank...what is wrong with doing it this way (assuming you are just padding out an integer, which was the question)?
DECLARE @intX int
SET @intX = 123
SELECT RIGHT(CONVERT(varchar,1000000 + @intX),5)
December 8, 2004 at 2:15 am
Oops, nothing in particular. Must have overseen it while scrolling up and down. Sorry!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 8, 2004 at 6:12 am
Too many good solutions to this questions... just as long as you keep the conversions down it's gonna be fine... I've seen 2-3 solutions here that do it in only 2 steps instead of 4-5. I'd use any of 'em since I've seen no speed difference whatsoever in any of 'em.
However I agree with Frank that this sould be done client side unless you need to keep this format on the server and that you must update all rows now.
December 8, 2004 at 7:45 am
Declare @RequiredLength int
Declare @ToConvert int
select @ToConvert = 22
select @RequiredLength = 5
select replicate('0', (@RequiredLength - len(@ToConvert))) + convert(varchar,@ToConvert)
December 8, 2004 at 7:47 am
Declare @RequiredLength int
Declare @ToConvert int
select @ToConvert = 22
select @RequiredLength = 5
select replicate('0', (@RequiredLength - len(@ToConvert))) + convert(varchar,@ToConvert)
December 8, 2004 at 1:17 pm
Ah, an int not a varchar...should have read carefully. And Frank and Mr. Robertson already answered with good solutions anyway. The UDF threw me off...
cl
Signature is NULL
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply