Kajal_T Kajal_T - 1 year ago 82
SQL Question

SQL Server: Better Solution for Column should have fixed length numeric data

I am trying to extract row which have only numbers in a specific column but the numbers should only be 6-digit or 12-digit.
I am trying:

select * from table where column like '[0-9][0-9][0-9][0-9][0-9][0-9]'

It works but seems a bit unclean to me.
Please see if anyone can provide better solution.

Many Thanks!

Answer Source

How about this?

where len(column) in (6, 12) and
      column not like '%[^0-9]%'

The first condition checks that the length is 6 or 12. The second checks that all characters are digits.

I should note that this is almost as clear:

where column like '[0-9][0-9][0-9][0-9][0-9][0-9]' or
      column like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download