check if all characters of a string are same

Consider the following example:


declare @t table(data VarChar(20))
insert into @t
select '3333333333' as data union all
select '55555436' union as data all
select 'xxxxxxxxxx'+CHAR(32) union as data all
select 'sddfgghfdhrsd' union as data all
select '0~~~~~~~~~~~~~' union as data all
select '555555555555' union as data all
select ']##########' union as data all
select ']    ' union as data all
select ']]]]]]]]]]]]]' as data


Here we see that only three values have all same characters. We can use many methods to find out this but the simplest method is:


select data from @t where PATINDEX('%[^'+left(replace(data,']',char(0)),1)+']%',replace(data,']',char(0)))=0


The result is
Data
--------------------
3333333333
555555555555
]]]]]]]]]]]]]


The logic is to see if there is a character which is different than the first character. If there is different character the patindex function will return a value greater than 0 otherwise all characters are same and it will return 0