Finding number such as 111111,0000,4444, using REPLICATE

Question

I have been trying to find repeated number such as 1111, 22222, 55555, in my table. After some research, I found that this is said to work:

SELECT *
FROM tablename
WHERE columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

It did work, but I want to understand why it works. If anyone understands what this is doing please enlighten me.

Answer 1

columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

E.g.

columnname = '111111' 
=>  
LEFT(columnname,1) = '1'
LEN(columnname)    = 6

REPLICATE(LEFT(columnname,1),LEN(columnname)) = 
REPLICATE(        '1'       ,       6       ) = 
'111111'

You take the leftmost character of the column and replicate it to the length of the column.
If the result is equal to the column then you know that all the characters of the column are the same of the leftmost character.