Left pad field
-
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.
-
Select right('00000' + cast(MyFieldName as varchar(5)), 5) as MyFieldName from MyTable
-
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] -
DECLARE @intX int
SET @intX = 123
SELECT RIGHT(CONVERT(varchar,1000000 + @intX),5)
------
00123
-
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
-
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))
-
And to bum Chad's a little more:
reverse(cast(reverse(REPLICATE('0', 5) + @value) as varchar(5)))
Signature is NULL
-
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] -
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)
-
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] -
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.
-
Declare @RequiredLength int
Declare @ToConvert int
select @ToConvert = 22
select @RequiredLength = 5
select replicate('0', (@RequiredLength - len(@ToConvert))) + convert(varchar,@ToConvert)
-
Declare @RequiredLength int
Declare @ToConvert int
select @ToConvert = 22
select @RequiredLength = 5
select replicate('0', (@RequiredLength - len(@ToConvert))) + convert(varchar,@ToConvert)
-
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