I have a column that will always be 5 digits long, and each digit will always be a 1 or a 0. I need to put in my where clause to exclude when the second position is equal to 1. For example 01000 is to be excluded but 10010 is to be kept. I currently have:
WHERE (SUBSTRING(field, 2, 1) <> '1') or field IS NULL
How do do this without using the Substring function?
Edit:Also, the column is a varchar(10) in the database. Does this matter?
You could use the like operator to check that character directly:
WHERE field LIKE '_1%' OR field IS NULL
Use LEFT and RIGHT and then check that is 1 or not as below-
WHERE RIGHT(LEFT(field,2),1) <> '1' OR field IS NULL
No. If 'field' is of a string type, you need to use string functions to manipulate it. SUBSTRING or some other flavor of it. You can also convert it to binary and use bitwise AND operator but that won't solve the root issue here.
You are facing the consequences of someone ignoring 1NF. There is a reason why Codd insisted that every "cell" must be atomic. Your's is not.
Can you separate this bitmap into atomic attribute columns?