SQL SERVER – Repeate String N Times Using String Function REPLICATE

I came across this SQL String Function few days ago while searching for Database Replication. This is T-SQL Function and it repeats the string/character expression N number of times specified in the function.

SELECT REPLICATE( ' http://www.SQLAuthority.com ' , 9 )

This repeats the string http://www.SQLAuthority.com to 9 times in result window. I think it is fun utility to generate repeated text if ever required.

Result Set:
http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com

(1 row(s) affected)

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

7 thoughts on “SQL SERVER – Repeate String N Times Using String Function REPLICATE

  1. Hi Sir,

    Your Article is very good. I got many tricks from this site.

    (Why cant you open Course for SQL Server like NIIT course?)

    I want to print one string in 100 times by row wise , how do we do?

    SELECT REPLICATE( ‘ http://www.SQLAuthority.com ‘ , 100 )–> This Prints in one row only.I want it print in 100 rows.

    Please give the Simple Query to this.

    Thanks and Regards
    Sunil. P

  2. Hi,
    We can use replicate for other uses also. For example if we want to sort a column which is having both numeric and varchar then,

    create table #tmptbl
    (
    x varchar(10)
    )

    insert into #tmptbl values (’1′)
    insert into #tmptbl values (’4′)
    insert into #tmptbl values (’9876′)
    insert into #tmptbl values (‘xyz’)
    insert into #tmptbl values (‘abc’)
    insert into #tmptbl values (’20′)
    insert into #tmptbl values (‘aeiou’)
    insert into #tmptbl values (’67′)
    insert into #tmptbl values (’6′)
    insert into #tmptbl values (’12′)

    select x
    from #tmptbl
    order by
    case when isnumeric(x)=1 then replicate(’0′,10-len(x))+x else x end

    Thanks
    Regards
    Viji

  3. Hey Viji

    It was really a gud one but the logic is not clear . Can you please explain .

    The replicate function gives the result
    000001
    000004
    009876
    000xyz
    000abc
    000020
    0aeiou
    000067
    000006
    000012
    but how does it orders it .

    Plzzzzz explain

    • Hi Ani,
      Logic behind that sorting as per my view is that when sorting starts in characters like 12 and 4 in this case sql server compares first character of both which is 1 and 4 in this situation 1 is smaller then 4 so it keeps record 12 before 4.
      But after concatenating 0′s like 04 and 12 now sql server will start comparing both the characters and first it will get 0 and 1 in this case 0 is < 1 and then it puts 04 first and then 12.(Like C language's string comparison function)

      Thanks
      Rahul Bhargava

  4. Hi Madhivanan,

    SELECT REPLICATE( ‘ http:://www.SQLAuthority.com’ , 100)

    when Results to Grid, this is replicating 100 times.

    when Results to Text, not replicating 100 times. Can u give me the reason.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s