2

I need to identify repeated numbers( Eg: 1111, 33333333, 5555555555,777777777 etc.) in a column.

How can I do this in sql server without having to hard code every scenario. The max length is 10 of the column. Any help is appreciated.

user2675491
  • 23
  • 1
  • 4
  • 2
    This could provide a solution to your problem: http://dba.stackexchange.com/questions/49522/how-to-find-column-with-all-same-repeating-character – Max Jul 10 '14 at 20:52

2 Answers2

9

This will check if the column has all the same value in it.

SELECT *
FROM tablename
WHERE columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

As Nicholas Cary notes, if the column is numbers you'd need to cast as varchar first:

SELECT *
FROM tablename
WHERE CAST(columnname AS VARCHAR(10)) = REPLICATE(LEFT(CAST(columnname AS VARCHAR(10)),1),LEN(CAST(columnname AS VARCHAR(10))))
Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
  • @Dave.Gugg: this works for string types, but not for integers. You'd have to convert the `int` column to `varchar` first. But it's about as good as you'll get in SQL. – Nicholas Carey Jul 10 '14 at 21:02
  • @NicholasCarey a couple of casts should be able to take care of it. – Darek Jul 10 '14 at 21:04
  • @NicholasCarey just tested on an INT and it worked fine for me. SQL2014. – SQLChao Jul 10 '14 at 21:04
  • Implicit cast kicked in, @JChao – Darek Jul 10 '14 at 21:05
  • @JChao: that's because there's an implicit conversion allowed in both directions (`int` -> `char` and `char` -> `int`). (1) You have no idea which conversion you get, and, (2) [the conversion can prohibit the use of any covering indices](http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/), because you now have an *expression* (a computed value) rather than a *column reference* – Nicholas Carey Jul 10 '14 at 21:19
  • Thank you all for responding. Dave's query resolved my issue. – user2675491 Jul 16 '14 at 21:15
1

Riffing on @Dave.Gugg's excellent answer, here's another way, using patindex() to look for a character different than the first.

select *
from some_table t
where 0 = patindex( '[^' + left(t.some_column,1) + ']' , t.some_column )

Again, this only works for string types (char,varchar, etc.). Numeric types such as int will need to be converted first.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135