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. The function is REPLICATE, and it is simple to use.
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)
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
10 Comments. Leave new
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://blog.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
Set the result mode to Text (Press Ctrl+T) and try this code
SELECT REPLICATE( ‘http://www.SQLAuthority.com ‘+ char(13) , 100 )
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
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
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')
SET @idx = @idx + 1
END
SELECT * FROM @TempTable
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.
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
Thanks for your comment Noopur.
It is really Helpful.