How to select * from table A, where column A values ​​do not start with the letter 'F'

Question

Can I get an SQL query to retrieve data from columna in table A whose values ​​do not start with 'f'?

For instance:

select * from tableA where columnA 

where the values ​​do not start with the letter "F".

Answer 1

 SELECT columnA FROM tableA WHERE SUBSTR(columnA,1,1) <> 'f' 

If you need both "f" and "F":

 SELECT columnA FROM tableA WHERE SUBSTR(columnA,1,1) NOT IN ('f','F') 

Based on the Lerxst example , some DBMSs will also allow you to do funny things like this:

 SELECT columnA FROM tableA WHERE columnA NOT LIKE ALL ('f%','F%') 

Answer 2

For an MSSQL script, you can use the "NOT" operator in conjunction with the LIKE operator. So your SQL would look something like

select * from tableA where columnA NOT LIKE 'F%'

Answer 3

@Evan: The assertion that SQL Server is case insensitive is actually not entirely true. Sensitivity depends on sorting. The server has sorting (selected during installation), the database has sorting (selected during database creation), and text columns have sorting (selected during column creation). If no mapping is specified for creating the database, the server will be configured by default. If sorting is not specified when creating a column, it gets the same value as the database.

But in most cases, people (fortunately) install their server using case-insensitive matching, such as Latin1_General_CI_AS. CI = case insensitive, AS = accent sensitive.

On SQL Server, if I needed to get both small f and capital F, I would go for:

 where columnA NOT LIKE 'F%' and columnA NOT LIKE 'f%' 

PS: I add this as an “answer” because I don’t see the possibility of commenting on the existing answer - I'm still new to this ... If anyone has an explanation why I do not understand this option, do not hesitate to contact me.

Regards, Valentino

Answer 4

I like all of the above ideas, but I usually use a different approach.

 SELECT * FROM tableA WHERE LEFT(columnA,1) <> 'F' 

T-SQL really offers a million ways to throw a cat.

Answer 5

Finding both F and f seems too complicated.

 SELECT * FROM tableA WHERE upper(substr(columnA,1,1)) != 'F' 

Or to quote my friend Richie - when searching in sql, crop it and then force it top