LIKE and REPLICATE

The LIKE operator can be used for a lot of good things. Recently, however, I’ve discovered the REPLICATE function which adds another dimension to what you can do. Simply put, the REPLICATE function takes a string and an integer input, and outputs the string repeated a number of times determined by the integer. The key thing here is that the string can be a wildcard from the pattern searches you can do with a LIKE or PATINDEX in SQL Server.

For instance REPLICATE(‘[0-9]’, 3) will output ‘[0-9][0-9][0-9]’ and this can be used as one side of a LIKE operator. Or it can be concatenated with other text.

As an example, when checking if the column Col1 is a decimal, the following code will check this for us:

SELECT

      *

FROM Table1

WHERE 1 = 1

AND Col1 LIKE ‘[-0-9]’ + REPLICATE(‘[0-9]’, LEN(Col1)-4) + ‘.[0-9][0-9]’

The ‘[-0-9]’ at the start checks that the first character is either numeric or a – sign. The REPLICATE part checks that the next X characters are numeric (where X is the length of the string – 4). The final ‘.[0-9][0-9]’ checks that the string ends with a decimal point followed by 2 integers.