How to check if all of the characters are the same in a field 3

[HoustonGuy]

Is there a function that will help me check a field for a repetition of characters in a single field?
Such as:

Field1
-------
AAAAAAAA
00000000
44444444

Currently I use substring to check to see if the first four characters = the last four characters.
If substring(Field1, 1, 4) = Substring(Field1, 5 len(Field1)) then 'All characters match.

Surely there is a function to check this.

Thanks.

 

[SkipVought]

If the sum of the ASCII value of each character divided by the length of the string equals the ASCII value of the first character, then all the characters are identical.

 

[HoustonGuy]

That is certainly creative - however I'm not sure that it would be a better option than comparing two substrings.

 

[Olaf Doschke]

Why should there be a function AllSAMECHARACTERS(field)? This is not a general purpose and often needed function at all.
The way you do also works for odd lengths, if you use the middle char in both substrings.

What might be more effective is REPLACE in the field, search the first character and replace with the empty string. If the result is empty you know there are no other characters.

Bye, Olaf.

 

[bborissov]

DECLARE @Test TABLE (TheField varchar(200))
INSERT INTO @Test VALUES('AAAAAAAA')
INSERT INTO @Test VALUES('00000000')
INSERT INTO @Test VALUES('44444444')
INSERT INTO @Test VALUES('44441444')
INSERT INTO @Test VALUES('000AA000')
INSERT INTO @Test VALUES('ADDAAAAA')


SELECT * FROM @Test
WHERE LEN(REPLACE(TheField,LEFT(TheField,1),'')) = 0

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Skip,

Could you elaborate on your comment. I think it does not work so it is likely that I don't understand.

Example, if the string was BAC.

Select ASCII('B'), ASCII('A'), ASCII('C')           -- 66 65 67
Select ASCII('B') + ASCII('A') + ASCII('C')         -- 198
Select (ASCII('B') + ASCII('A') + ASCII('C')) / 3   -- 66

In this example, the results of the algorithm would be 66 which is the ASCII value of the first character.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

I appreciate ALL feedback!
Olaf, I'm not INSISTING there should be a function. I'm simply ASKING.

Borislave - that is very cool! Much appreciated!

 

[SkipVought]

It's a Rube Goldberg.

 

[PWise]

try
REPLICATE ( left(Field1,1),len(Field1) ) =Field1

 

[HoustonGuy]

That's very clever PWise! Thanks for that option!