SQL SERVER – Script to Find Strings with Same Characters

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)