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
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( ‘ https://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.