Solved

Left Pad a String

Posted on 2006-06-15
5
3,225 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
5 Comments
 
LVL 3

Assisted Solution

by:bharatbutani
bharatbutani earned 100 total points
Comment Utility
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 300 total points
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
Trying now...
Will post again soon.
0
 
LVL 8

Author Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now