One of my blog followers asked me if it is possible to check whether all the characters of a column value is same. For example if there are values like KKK and KKT, the value KKK should be the output as all characters are same.
There is a method without using WHILE Loop
Create the following data set
CREATE TABLE #TEMP(COL VARCHAR(20))
INSERT INTO #TEMP
SELECT 'AAAAA' AS DATA UNION ALL
SELECT 'NKKKKKKKKKK' UNION ALL
SELECT '32000000' UNION ALL
SELECT '11111111' UNION ALL
SELECT '1002' UNION ALL
SELECT 'OOOOO'UNION ALL
SELECT 'M'
Run the following code
SELECT
COL FROM #TEMP
WHERE
REPLACE(COL,LEFT(COL,1),'')=''
The result is
COL
--------------------
AAAAA
11111111
OOOOO
M
The logic is to get first character and replace that character by empty string in the column. If the final result is empty string, it means that all characters are same. Otherwise all characters are not same.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Hi Pinal,
Nice one.
Thanks @NAGARAJ
Although I have never had such a requirement, it sure is interesting! Thanks!
I agree nakul
Nice!!
Thanks @Goria
Much appreciated, Pinal.
Thanks, that is a good one