{"id":4027,"date":"2019-05-16T21:49:07","date_gmt":"2019-05-17T04:49:07","guid":{"rendered":"https:\/\/database.guide\/?p=4027"},"modified":"2019-11-19T22:57:05","modified_gmt":"2019-11-20T05:57:05","slug":"how-the-replicate-function-works-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-the-replicate-function-works-in-sql-server-t-sql\/","title":{"rendered":"How the REPLICATE() Function Works in SQL Server (T-SQL)"},"content":{"rendered":"

In SQL Server<\/a>, the T-SQL<\/a>\u00a0REPLICATE()<\/code> function repeats a string value a specified number of times and returns the result. <\/p>\n

The function accepts two arguments; the input string, and the number of times it should be repeated.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

REPLICATE ( string_expression ,integer_expression )<\/pre>\n
Where string_expression<\/var> is the input string. This can be either character or binary data. <\/p>\n
And integer_expression<\/var> is an integer that specifies how many times to repeat the input string. This can any integer type, including bigint<\/strong>.<\/p>\n
Note that if the first argument is not of type varchar(max)<\/strong> or nvarchar(max)<\/strong>, the function truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first argument must be explicitly cast to the appropriate large-value data type.<\/p>\n
Example 1 – Basic Usage<\/h2>\n
Here’s an example of how it works:<\/p>\n
SELECT REPLICATE('Dog', 3) AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+-----------+\r\n| Result    |\r\n|-----------|\r\n| DogDogDog |\r\n+-----------+\r\n<\/pre>\n
Example 2 – Add a Space<\/h2>\n
We can also add a space to the previous example:<\/p>\n
SELECT REPLICATE('Dog ', 3) AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+--------------+\r\n| Result       |\r\n|--------------|\r\n| Dog Dog Dog  |\r\n+--------------+\r\n<\/pre>\n
Although note that this will add a space at the end of the string too.<\/p>\n
We could use the TRIM()<\/code> function to overcome this:<\/p>\n
SELECT TRIM(REPLICATE('Dog ', 3)) AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+-------------+\r\n| Result      |\r\n|-------------|\r\n| Dog Dog Dog |\r\n+-------------+\r\n<\/pre>\n
Maybe the following example will make this more apparent:<\/p>\n
SELECT \r\n  REPLICATE('Dog ', 3) + '.' AS 'Untrimmed',\r\n  TRIM(REPLICATE('Dog ', 3)) + '.' AS 'Trimmed';<\/pre>\n
Result:<\/p>\n
\r\n+---------------+--------------+\r\n| Untrimmed     | Trimmed      |\r\n|---------------+--------------|\r\n| Dog Dog Dog . | Dog Dog Dog. |\r\n+---------------+--------------+\r\n<\/pre>\n
Example 3 – Invalid Replication Count<\/h2>\n
If the second argument is a negative value, NULL<\/code> is returned:<\/p>\n
SELECT REPLICATE('Dog', -3) AS Result;<\/pre>\n
Result:<\/p>\n
\r\n+----------+\r\n| Result   |\r\n|----------|\r\n| NULL     |\r\n+----------+\r\n<\/pre>\n
Example 4 – A Database Example<\/h2>\n
Here’s an example of replicating data from a database:<\/p>\n
\r\nSELECT TOP(3) \r\nTRIM(REPLICATE(ArtistName + ' ', 3)) AS Result\r\nFROM Artists;\r\n<\/pre>\n
Result:<\/p>\n
\r\n+----------------------------------------------------+\r\n| Result                                             |\r\n|----------------------------------------------------|\r\n| Iron Maiden Iron Maiden Iron Maiden                |\r\n| AC\/DC AC\/DC AC\/DC                                  |\r\n| Allan Holdsworth Allan Holdsworth Allan Holdsworth |\r\n+----------------------------------------------------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, the T-SQL\u00a0REPLICATE() function repeats a string value a specified number of times and returns the result. The function accepts two arguments; the input string, and the number of times it should be repeated.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,67,61],"class_list":["post-4027","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-string-functions","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4027","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=4027"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4027\/revisions"}],"predecessor-version":[{"id":4032,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/4027\/revisions\/4032"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=4027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=4027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=4027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}