Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

HoustonGuy

Programmer
Jun 29, 2000
165
US
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. :)
 
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.
 
That is certainly creative - however I'm not sure that it would be a better option than comparing two substrings. :)
 
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.
 
Code:
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
 
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.

Code:
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
 
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!
 
try
REPLICATE ( left(Field1,1),len(Field1) ) =Field1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top