Searching for Invalid Values in a Column

[drallabs]

I would like to find the following occurences of invalid values in a field.

The first occurence is I have a field that limits the number of characters to 7. However users sometimes enter values in this field that are 6 characters or less. Is there a way to query these out?

The second occurence is users may enter lower case characters, in the field mentioned above, when they should in fact be capitalized. Is there a query to find these lower case characters?

Thanks.

 

[gmmastros]

declare @Temp Table(Data varchar(7))

Insert Into @Temp Values('ABCDEFG')
Insert Into @Temp Values('ABCDeFG')
Insert Into @Temp Values('ABCDEF')

Select * 
From   [!]@Temp [/!]
Where  Len([!]Data[/!]) <> 7
       Or Convert(VarBinary(100), Upper([!]Data[/!])) <> Convert(VarBinary(100), [!]Data[/!])

If you are satisfied with this functionality, you will need to change the table name and the column name (highlighted in red)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom