Check If Field in Sql Contains Specific Alphanumeric Format
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...
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!
2 Answers
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
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]%'
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
