5

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.

5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.