by Tony Marasco

Using REPLICATE

The REPLICATE statement copies or “replicates” a string of characters a given number of times.

Syntax
REPLICATE ( character_expression ,integer_expression )

Arguments

character_expression
Is an alphanumeric expression of character data or of a data type that is implicitly convertible to varchar. Otherwise, use CAST to convert explicitly character_expression. character_expression can be a constant, variable, or column of either character or binary data.

integer_expression
Is a positive whole number. If integer_expression is negative, a null string is returned. integer_expression can be of type bigint.

For example, the statement:

SELECT REPLICATE('Test',3)

Displays: TestTestTest

Use in Precision.BI – Object Expressions or Metadata Expressions

The REPLICATE command may be used with a few other SQL commands to provide capabilities such as leading zeroes to numbers. For example, consider a series of numbers that have variable lengths and you want them to be nine-digit numbers with leading zeroes. The function below will provide the results as shown. In the example, the SchVisitNum is a numeric value and needs to be converted to a text value – otherwise the values will be added.

The above example is quite long, but there are a few arguments to change. The green text is the data field from the Treeview that will have the leading zeroes applied. The purple nine is the number of characters, in total, you want displayed. For example, a seven-digit visit number will have two leading zeroes – a total of nine characters.

The following table shows results of the above statement.

	Original Visit Number	Result of Replicate
	1716	000001716
	11954	000011954
	111413	000111413