Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Location
    California
    Posts
    9

    Unanswered: Sybase functions

    Is there a function in Sybase similar to Oracle funcions LPAD and RPAD?

    Also I want to know how can I format a number into a string usign currency formats or whatever, like the TO_CHAR fucntion with format '999.999.999'

    Thanks.

  2. #2
    Join Date
    Jan 2005
    Location
    California
    Posts
    9

    Example of what I want to do

    This will be the query in Oracle to solve what I want to do:

    select to_char('19923','999.999') || to_char(sysdate,'yyyymmdd') || lpad('john smith', 30) from dual;

    The result will be:
    019.92320050111 john smith

    (john smith plus the spaces equals 30 characters).


    How can I do this in a SyBase stored procedure?

    I know how to format de date with DateFormat function, but I dont know how to format the numeric value neither how to pad right or left a field to a exact number of characters.

    Please help!

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I looked through the docs and didn't see an LPAD function.

    I think you'll have to do something like:
    replicate( ' ', 30 - char_length( your_column ) ) + your_column

    You might also be able to utilize the stuff function to do that as well:
    stuff( your_column, 0, 0, replicate( ' ', 30- char_length( your_column ) ) )

    Or some flavor of both. If your ASE supports Java-in-DB, you could write a Java function to do that as well.
    Thanks,

    Matt

  4. #4
    Join Date
    Jan 2005
    Location
    California
    Posts
    9
    I have writen this stored function instead to solve the problem:

    create function DBA.padleft(@Value varchar(255), @PadChar varchar(1), @NewLength integer)
    returns varchar(4000)
    begin
    declare @i integer;
    declare @Result varchar(4000);

    if @Value is null then
    set @Result = repeat(@PadChar,@NewLength);
    else
    set @i = length(@Value);
    set @Result = isnull(@Value,' ');
    while @i < @NewLength loop
    set @Result = string(@PadChar, @Result);
    set @i = @i + 1;
    end loop;
    end if;

    return @Result;
    end;


    And now I can do this as much times as i need:

    select padleft(fieldname, ' ', 20) from dummy;

    Yeah!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •