Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb Unanswered: how to repeat a string

    Please suggest me the equivalent for the below DB2 query in Oracle


    with temp (name ) as (values repeat(padding,len))
    select substr(name || rtrim(coalesce(str,' ')),length(rtrim(coalesce(str,' ')))+1,len) from temp

    This query will select the column 'name' and on this column repeat(padding,len) action should be performed

  2. #2
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb oracle equivalent of REPEAT(String,integer)

    I have to perform a task in oracle which involves using REPEAT kind of statement.
    what is the oracle equivalent of REPEAT(String,integer)
    in DB2
    SELECT LENGTH(REPEAT('rr4', 5))
    FROM SYSIBM.SYSDUMMY1;
    this will return '15' as result,
    the same thing i have to get in Oracle

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For those of us whom are not familiar with oracle, could you be more specific. Maybe give examples of what you expect it to do.

    Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by krReddy

    with temp (name ) as (values repeat(padding,len))
    select substr(name || rtrim(coalesce(str,' ')),length(rtrim(coalesce(str,' ')))+1,len) from temp

    This query will select the column 'name' and on this column repeat(padding,len) action should be performed
    Despite your genuine attempt to confuse us, I think I understand what you are trying to achieve. You should try Oracle's RPAD function. By the way, asking this question in the Oracle forum might have been more productive.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb how to repeat a string

    I need to repeat a string by 'N' no of times. In DB2 i can perform this by using a REPEAT statement.
    i.e REPEAT('str',3)-------o/p is strstrstr

    similar thing i have to get in Oracle. could you please suggest me the equivalent for this in Oracle.

  6. #6
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb repeat()

    sorry for the confusion,

    in DB2 REPEAT('we',3)---gives o/p as 'wewewe'

    similar o/p i have to get in Oraclr.Which function i have to use for this.

  7. #7
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb Oraclr function to repeat a string

    I need to repeat a string by 'N' no of times. In DB2 i can perform this by using a REPEAT statement.
    i.e REPEAT('str',3)-------o/p is strstrstr

    similar thing i have to get in Oracle. could you please suggest me the equivalent for this in Oracle.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do that with the RPAD function, though it works slightly differently:

    Code:
    SQL> select rpad('str',9,'str') from dual;
    
    RPAD('STR
    ---------
    strstrstr
    It means: take the string 'str' (arg 1) and pad it out to the right with string 'str' (arg 3) repeatedly until the total length is 9 (arg 2).

    You could use that to build your own REPEAT function like this:
    Code:
    SQL> create or replace function repeat
      2  ( p_string varchar2
      3  , p_times  integer
      4  ) return varchar2
      5  is
      6  begin
      7    return rpad (p_string, length(p_string)*p_times, p_string);
      8  end;
      9  /
    
    Function created.
    
    SQL> select repeat('str',3) from dual;
    
    REPEAT('STR',3)
    -------------------------------------------------------------------
    strstrstr

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See my answer to your duplicate post.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    threads merged (again!)

    krReddy, please stop creating new threads for the same question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2005
    Posts
    10
    try rpad function.

Posting Permissions

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