Check if field in SQL contains specific alphanumeric format

Question

I need to test a specific Column in a SQL table to look for any variances outside of a specific alphanumeric format. In this field, the correct format should be one alpha character followed by six numbers, like G123456. the first letter could be anything in the alphabet (upper or lower case), and the number could be any sequence of six digits. But the pattern is always one alpha followed by six digits.

I have done some searching but have not been able to come up with a solution. Any suggestions would be welcome!

I am running SQL server 2008 R2.

Thanks!

Solution 1

If you want to inspect the existing data for variances that fall outside of your expected format, you can query like this:

SELECT * FROM [dbo].[TableName] 
WHERE [FieldName] NOT LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'

If you want to prevent invalid data getting into the table in the first place, you could add a check constraint:

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [CK_TableName_FieldName] 
CHECK  (FieldName like '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9]' )
GO

Solution 2

This SQL query will give you all Alpha-Numeric Name in SampleTable:

select XYZ_ColumnName from DBName.Schema.SampleTable
where Name like '%[^a-z][^A-Z][^0-9]%'

Comments

[Mike Marshall]

For now, this is the simplest way to meet this particular need. Thank you!

[Mike Marshall]

I cannot prevent invalid data, but I do want to find it, then research what the right information is, and correct it. The research is all manual but this will help be find where the errors are. Thanks again!