Friday, July 8, 2011

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

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

1 comment:

  1. Very handy! I used this to validate phone numbers. Sometimes people will enter bogus numbers where all of the digits are the same. This takes care of it.