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.
Start Free Trial
