CODEWITHSUNDEEP
sqldatabase
Raghavendra Gupta
Raghavendra Gupta

Reputation: 355

Check if the column has single value that is repeating itself

I'm working on a script to identify the column values that have

  1. single value with repeating itself and there is no other value in that column.

For example,

00,
111,
2222,
33333,
444444,
5555555,
66666666,
777777777,
8888888888,
99999999999,
00000000000,
9999999999,
88888888,
7777777

Any value that is repeating it multiple times and has no other digit in that column; seeking help for a generic function or logic to test the same.

We can ignore any other values such as: 123123123 or 123454321

Thanks.

Upvotes: 1

Views: 182

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

One method is:

select t.*
from t
where replace(col, left(col, 1), '') = '';

This replaces the first character. If nothing is left, then all values are the same.

Note: Most databases support replace() and left(). Those that do not have equivalent functionality with different function names.

Upvotes: 6

Related Questions