find rows that contain x numbers in a row
I need to find any rows in a table that have 16 numbers in a row.I need to make sure the users have not already inserted rows that contain a cc number. I am going to replace all spaces and dashes with empty string and manually inspect the rows to ensure i only act on appropriate ones. We 开发者_开发知识库have several million rows and I need to narrow down the focus.
...
WHERE column_name LIKE REPLICATE('[0-9]', 16);
If you want the ones that are not like a valid credit card number, then:
...
WHERE column_name NOT LIKE REPLICATE('[0-9]', 16);
Of course, remember that American Express cards only have 15 digits. So to incorporate that, you may want instead:
...
WHERE column_name LIKE REPLICATE('[0-9]', 16)
OR column_name LIKE '3' + REPLICATE('[0-9]', 14);
EDIT Based on t-clausen.dk's interpretation of the problem, where the OP hasn't already replaced dashes and spaces, you may need:
...
WHERE REPLACE(REPLACE(column_name, '-', ''),' ','') LIKE ...
精彩评论