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
pinaldave
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
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