SQL Query - how not to include some results

Question

I apologize if this question was asked before I just could not formalize it correctly. I have a column of code in a table and you want to query it, but delete some elements with specific code. Let's say I want to take elements with a code starting at 4, but not include elements with a code whose 6th number is 9 (1121290).

The code column contains a string of numbers with a maximum length of 8 char. and I want to take almost everything that starts with 4, with the exception of elements starting with 411, 427 and 428

Answer 1

Yes, you can ask this request: -

I have a column element with 6 digit codes.

I get an element whose first digit is 4 or 6, equal to 9.

we must use % and _ to select.

 SELECT element FROM "table" WHERE element LIKE '%4____9%'; 

try it, it will work.

Everything that starts with 4, with the exception of elements starting with 411, 427 and 428: -

  SELECT element FROM "table" WHERE element LIKE '%4__'; 

The 1st digit is 4, and the 6th is not 9: I tested this, it works fine, try the following: -

 SELECT element FROM "table" WHERE element NOT LIKE '%_____9' and element LIKE '%4_____' 

Answer 2

The easiest way is to simply specify each condition in the where clause:

 WHERE code like '4____9%' AND code NOT LIKE '411%' AND code NOT LIKE '427%' AND code NOT LIKE '428%' 

Additional conditions will not greatly affect query performance; it will check every line starting at 4 anyway.

Answer 3

Assuming the CODE field is varchar and you are using SQL Server, you can use the query below

 select * from yourTable where code like '4%' AND CHARINDEX('9',code)<>6 

Answer 4

Here is a more compact version.

 `SELECT * FROM table_name WHERE code IN ('4%') and code NOT IN ('411%','427%', '428%','_____9%');`