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( ' https://blog.sqlauthority.com/ ' , 9 )

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

Result Set:
https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/ https://blog.sqlauthority.com/

(1 row(s) affected)

Reference : Pinal Dave (https://blog.sqlauthority.com/) , BOL

SQL Function, SQL Scripts, SQL String
Previous Post
SQLAuthority News – Book Review – Microsoft(R) SQL Server 2005 Unleashed (Paperback)
Next Post
SQL SERVER – Explanation and Example Four Part Name

Related Posts

10 Comments. Leave new

  • 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

    Reply
  • 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

    Reply
    • 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

      Reply
  • Nguyen Truong Thin
    April 7, 2010 12:47 pm

    Hi Sunil.P,

    This is code:

    DECLARE @TempTable AS TABLE
    (
    MyString varchar(256)
    )

    DECLARE @idx int
    SET @idx = 1

    WHILE ( @idx <= 100)
    BEGIN
    INSERT INTO @TempTable VALUES('http://www.SQLAuthority.com&#039;)
    SET @idx = @idx + 1
    END

    SELECT * FROM @TempTable

    Reply
  • 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.

    Reply
  • Try like this :

    create table #tmptbl
    (
    x varchar(10)
    )
    insert into #tmptbl values (‘1′)
    insert into #tmptbl values (’40’)
    insert into #tmptbl values (‘xyz’)
    insert into #tmptbl values (’12’)
    insert into #tmptbl values (‘aeiou’)
    insert into #tmptbl values (‘$$$$$’)
    select
    replicate(‘0’,10-len(x))+x
    from #tmptbl
    order by x

    Reply
  • Sumit Chahal
    May 11, 2016 5:47 pm

    It is really Helpful.

    Reply

Leave a Reply