If Statements in ArcPy Select Feature SQL Query

Question

I'm trying to select pipes with specific codes. These codes are 8 digits long and I only need to select certain combinations. For example, pipes that have codes with the number 8 in the 7th position of the code need to be selected, meaning that a pipe with a code such as 02004580 would be selected. In total there are 4 different cases of codes that would require selection.

I don't know much about SQL but I tried to use the SelectLayerByAttribute tool and then input a query that would select pipes with codes that had an 8 in the 7th position but it did not work:

SELECT*FROM w_pipe WHERE:
SELECT SUBSTRING(CRITICAL_CODE,7,1) =8

The "SELECT*FROM w_pipe WHERE:" is what comes up automatically in the Select by Attributes window. I have a feeling the SelectLayerByAttribute tool might not support the SQL statement that I entered but I'm thinking I just didn't input the proper code.

enter image description here

• Andy: Welcome to GIS SE. Have you looked at Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10?

Answer 1

You only need to supply the contents of the WHERE clause. So while the whole SQL query would be:

SELECT * FROM w_pipe WHERE
SUBSTRING(CRITICAL_CODE, 7, 1) = '8'

All that is actually needed is:

SUBSTRING(CRITICAL_CODE, 7, 1) = '8'

Notice that I also enclosed the 8 in single quotes, because it will have to be a string data dype in order to be compared t another string, which SUBSTRING returns.