CODEWITHSUNDEEP
sqlpostgresql
tuazku
tuazku

Reputation: 129

SQL Query - How to not include some results

I apologize if this question was asked before I just couldn't correctly formalize it. I have a code column in a table and want to query it but remove some elements with some particular code. Say I want to take elements with code starting from 4 but not include the elements with code whose 6-th number is 9 (1121290).

The code column contains string of numbers with max-length of 8 char. and I want to take almost everything that starts with 4 except elements that start with 411, 427 and 428

Upvotes: 2

Views: 11631

Answers (4)

FrontEnd Expert
FrontEnd Expert

Reputation: 5813

Yes you can give query like this:--

I have column element with 6 digit codes.

I am fetching element whose 1st digit is 4 or 6th is 9.

we have to use % and _ for fetching..

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

try this it will work.

Everything that starts with 4 except elements that start with 411, 427 and 428 :-

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

1st digit is 4 and 6th is not 9: I tested this one it is working fine try this :-

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

Upvotes: 1

Amogh Talpallikar
Amogh Talpallikar

Reputation: 12184

Here is a more compact version.

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

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102793

It might be easiest to simply spell out 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%'

The extra conditions won't hurt the query's efficiency much; it's going to have to scan every single row starting with 4 anyway.

Upvotes: 1

avani gadhai
avani gadhai

Reputation: 460

Assuming that your "CODE" field is varchar and you are using SQL Server, you can use the below Query

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

Upvotes: 0

Related Questions