Often I need to justify a number or string to the left, right, or center it to a certain width.
Example: Right justify a number with zeroes so it is 5 digits wide, or center fill a string with "-" so it looks like a title.

The function accepts 3 parameters:
1) aValue is the string you wish to fill. If you want to fill a number then you need to use Cast(MyNumber as VarChar) as the parameter.

2) aWidth is the width the result should be

3) aFillChar is an optional character that is used for filling. If excluded, it defaults to a space. If you want to RightJustify a number, use '0'.

Note: If you want to right justify a number like 100, then just use:
rightjustify(cast(100 as VarChar),5,'0')

Barry

CREATE FUNCTION "LeftJustify" (IN "aValue" VARCHAR COLLATE UNI_CI, IN "aWidth" INTEGER, IN "aFillChar" CHAR(1) COLLATE UNI_CI)
RETURNS VARCHAR COLLATE UNI_CI
BEGIN
 Declare Result VarChar default null;
 set Result = Trim(both ' ' from aValue);
 if length(Result) < aWidth then
   set Result = Result + Repeat(IFNULL(aFillChar,' ',aFillChar), aWidth-length(Result));
 end if;
 Return Result;
END

CREATE FUNCTION "RightJustify" (IN "aValue" VARCHAR COLLATE UNI_CI, IN "aWidth" INTEGER, IN "aFillChar" CHAR(1) COLLATE UNI_CI)
RETURNS VARCHAR COLLATE UNI_CI
BEGIN
 Declare Result VarChar default null;
 set Result = Trim(both ' ' from aValue);
 if length(Result) < aWidth then
   set Result = Repeat(IFNULL(aFillChar,' ',aFillChar), aWidth-length(Result)) + Result;
 end if;
 Return Result;
END

CREATE FUNCTION "CenterJustify" (IN "aValue" VARCHAR COLLATE UNI_CI, IN "aWidth" INTEGER, IN "aFillChar" CHAR(1) COLLATE UNI_CI)
RETURNS VARCHAR COLLATE UNI_CI
BEGIN
 Declare Result VarChar default null;
 Declare _Left  VarChar default NULL;
 Declare _Right VarChar default NULL;
 set Result = Trim(both ' ' from aValue);
 if length(Result) < aWidth then
   set _Left  = Repeat(IFNULL(aFillChar,' ',aFillChar), Ceil((aWidth-length(Result))/2));
   set _Right = Repeat(IFNULL(aFillChar,' ',aFillChar), aWidth-Ceil((aWidth-length(Result))/2)-length(Result));
   set Result = _Left + Result + _Right;
 end if;
 Return Result;
END

Barry


Neat, but, since I assume its for use in SELECTs rather than UPDATEs I'm can't see anywhere I'd use the LeftJustify function.

Roy Lambert

Barry,

<< Often I need to justify a number or string to the left, right, or center
it to a certain width.
Example: Right justify a number with zeroes so it is 5 digits wide, or
center fill a string with "-" so it looks like a title. >>

Nice, thanks.

Tim Young
Elevate Software
www.elevatesoft.com

>>Neat, but, since I assume its for use in SELECTs rather than UPDATEs I'm can't see anywhere I'd use the LeftJustify function.<<

Roy,

I put it in there because if I didn't, you'd be asking "where is it"?

Barry

Barry


>I put it in there because if I didn't, you'd be asking "where is it"?

I need an image consultant!!!!!!

Roy