Sybase ASE: Query to find correct format issue.

Hi Team ,

I am new to Sybase Adaptive Server Enterprise/15.7 (ASE) and need some guidance to find the different values in serial format column.

SELECT DISTINCT SERIAL_FORMAT FROM PRODUCTS

It has values with below format which contains 12 digits hexadecimal characters with numbers/alphabets.
Ex:

• 00:71:48:D1:R6:Z7 --Valid record
• 3E67E5E334Q8 --invalid as : is not used across 2 digits

How can query be written to identify SERIAL_FORMAT with:-

1. Total Valid
2. Invalid
3. Missing
4. Bad format
5. Repeated values

Any help is appreciated.

I think pattern matching would be one of many ways to check.
For example, this statement would count number of valid records:

 select COUNT(*) 
 from PRODUCTS
 where SERIAL_FORMAT like "__:__:__:__:__:__"
 

I don't know what are the valid characters in between the colons, so I use underscore (any character), you can change that to valid range using

[]

brackets.
For example

[A-Z]

would mean any character between A and Z inclusive.

In addition to what migurus has already said, note that saying that valid records contain 12 hexadecimal digits and then saying that a string containing 17 characters of which 7 are not hexadecimal digits (five colons, an uppercase R, and an uppercase Z) is valid AND saying that a string that doesn't contain colons is invalid brings everything else you've said into question. It is very hard to help you write code to help you test conflicting requirements.