?
Solved

Left Pad a String

Posted on 2006-06-15
5
Medium Priority
?
3,251 Views
Last Modified: 2010-10-13
Hello,
I have a string that I need to left pad with zeros.
The string actually represents a number value. The string is length 30.
I need a query to return the string left padded to the length of the largest string. example as below.

129736
13085
1308

these would be rreturned as

129736
013085
001308

Now.. This isnt for MS SQL but for Advantage Database Server. I know this is the MS SQL T/A but I thought it would be the best place to get an answer.
There are no padding functions available. The following is a list of available functions.

CONVERT( expr, data-type )      Returns the value of expr converted to data-type. The data-type parameter can be SQL_BINARY, SQL_VARBINARY, SQL_BIT (logical), SQL_VARCHAR, SQL_CHAR, SQL_DATE, SQL_DOUBLE, SQL_INTEGER, SQL_NUMERIC, SQL_TIME, or SQL_TIMESTAMP.
ASCII( str )      Returns integer ASCII value for single character at start of str.
BIT_LENGTH( str )      Returns length of str in bits (assumes 8-bit characters).
CHAR( int )      Returns a character value from ASCII integer.
CHAR_LENGTH( str )      Returns the length of string in characters.
CHARACTER_LENGTH( str )      Returns the length of string in characters.
CONCAT( str1, str2 )      Returns a string that is the result of concatenating str2 to str1.
INSERT( str1, start, len, str2 )      Returns a string where str2 into str1 at position start for at most len characters, replacing len characters.
LCASE( str )      Returns string with all uppercase characters converted to lowercase.
LEFT( str, count )      Return the left part of string str up to count characters.
LENGTH( str )      Returns the number of characters in str, excluding trailing blanks.
LOCATE( str1, str2[, start] )      Return integer location (1-based) of str1 in str2, with optional start starting point.If str1 is not found in str2, 0 is returned.
LTRIM( str )      Returns string with leading spaces from left side of string.
OCTET_LENGTH( str )      Returns length of string in octets (i.e., bytes).
POSITION( str1 IN str2 )      Returns the position of str1 in str2.
REPEAT( str, cnt )      Returns a string consisting of str repeated up to cnt times.
REPLACE( str1, str2, str3 )      Replace all occurrences of str2 in str1 with str3.
RIGHT( str, count )      Returns a string of count characters of right part of str.
RTRIM( str )      Returns a string with trailing spaces removed from right side of string.
SPACE( count )      Returns a string of space repeated up to count times.
SUBSTRING( str, pos, len )      Returns a portion of str starting at pos, up to length len.
UCASE( str )      Returns a string with all lowercase characters converted to uppercase.
UPPER (str)      Returns a string with all lowercase characters converted to uppercase.
ABS( num )      Returns the absolute value of a numeric.
ACOS( float )      Returns the arccosine of float as an angle, expressed in radians.
ASIN( float )      Returns the arcsine of float as an angle, expressed in radians.
ATAN( float )      Returns the arctangent of float as an angle, expressed in radians.
ATAN2( float1, float2 )      Returns the arctangent of the x and y coordinates, specified by float1 and float2, as an angle, expressed in radians.
CEILING( num )      Returns the smallest integer greater than or equal to num. The return value is of the same data type as the input parameter.
COS( float )      Returns the cosine of float, where float is an angle expressed in radians.
COT( float )      Returns the cotangent of float, where float is an angle expressed in radians.
DEGREES( num )      Returns the number of degrees converted from radians.
EXP( float )      Returns the exponential value of float.
FLOOR( num )      Returns the largest integer less than or equal to num. The return value is of the same data type as the input parameter.
LOG( float )      Returns the natural logarithm of float.
LOG10( float )      Returns the base 10 logarithm of float.
MOD( int1, int2 )      Returns the modulus (remainder) of int1 divided by int2.
PI( )      Returns the value of pi as a floating point value constant.
POWER( num, int )      Returns the value of num raised to the power of int.
RADIANS( num )      Returns the number of radians converted from degrees.
RAND( [int] )      Returns a random floating point value using int as an optional seed value. If zero is specified as the seed value, the current time in milliseconds is used as the seed.
ROUND( num, int )      Returns num rounded to int places to the right of the decimal point. If int is negative, num is rounded to ABS(int) places to the left of the decimal point.
SIGN( num )      If num is less than zero, –1 is returned. If num equals zero, 0 is returned. If num is greater than zero, 1 is returned.
SIN( float )      Returns the sine of the float expression, where float is an angle expressed in radians.
SQRT( float )      Returns the square root of float.
TAN( float )      Returns the tangent of float, where float is an angle expressed in radians.
TRUNCATE( num, int )      Returns num truncated to int places to the right of the decimal point. If int is negative, num is truncated to ABS(int) places to the left of the decimal point.

This probably easy, but my head hurts.
Thanks for any help.
0
Comment
Question by:RickJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Assisted Solution

by:bharatbutani
bharatbutani earned 400 total points
ID: 16917614
Hey RickJ

I think this would help ypu solve your problem .....

I am taking an example from the PUBS database of SQL Server... I am trying to do that with the au_lname column of the authors table ......

Select au_lname from authors
======================
Bennet
Blotchet-Halls
Carson
DeFrance
del Castillo
Dull
Green


Next I select the Max Length of the data in the column...


Select max(len(au_lname)) from authors
=============================

14


& Then

Select right('00000000000000' + Cast(au_lname as varchar(14)),14),au_lname from authors
===============================================================

00000000Bennet
Blotchet-Halls
00000000Carson
000000DeFrance
00del Castillo
0000000000Dull
000000000Green



Regards
Bharat Butani.
0
 
LVL 21

Accepted Solution

by:
Yurich earned 1200 total points
ID: 16917658
you can try this:

-- the longest number
declare @max_length int
set @max_length = ( select max( len(  <column_name> )) from <table_name> )

-- padding is as long as the longest number
declare @padding varchar( 32 )
set @padding = '000000000000000000000000000000'
set @padding = left( @padding, @max_length )

-- actual padding
select right( @padding + <column_name> ), @max_length ) from <table_name>

good luck,
yurich
0
 
LVL 5

Assisted Solution

by:Z03niE
Z03niE earned 400 total points
ID: 16917669
I'll just give another alternatif :

select
right(rtrim('000000000000000000000000000000'+CONVERT(urfield,varchar(30))),
(select max(len(urfield)) from urtable))
from urtable
0
 
LVL 8

Author Comment

by:RickJ
ID: 16917724
Trying now...
Will post again soon.
0
 
LVL 8

Author Comment

by:RickJ
ID: 16918051
Thanks for the help.
I used Yurichs example. But all helped me get there.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question