how to select * from tableA where columnA values don't start with letter 'F'

Question

Can I have a SQL query to get the data from columna in tableA whose values don't start with 'f' ?

For example:

select * from  tableA where columnA

where values don't start with 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')

Going off of Lerxst's example, some DBMSs will also let you do fun stuff like this:

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

Answer 2

For a MSSQL Scenario, you should be able to use the "NOT" operator in conjunction with the LIKE operator. So your SQL would look roughly like

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

Answer 3

@Evan: the statement about SQL Server being case insensitive is actually not entirely true. Case sensitivity depends on collation. The server has a collation (chosen on install), a database has a collation (chosen on DB creation) and text columns have a collation (chosen when creating the column). When no collation is specified on DB creation, the server collation will be the default. When no collation specified on column creation it gets the same collation as the DB.

But in most cases, people (luckily) install their server using a case insensitive collation, such as Latin1_General_CI_AS. CI = case insensitive, AS = accent sensitive.

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

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

PS: I'm adding this as "answer" because I don't see any option to comment on an existing answer - I'm still new here... If anyone has an explanation why I don't get this option, don't hesitate to contact me.

Regards, Valentino.

Answer 4

SELECT *
FROM tableA
WHERE columnA NOT LIKE 'F%'

This should work on all SQL-92 compliant databases, and should work with upper/lower case "F" in most situations. If you have created an index on columnA, this query will leverage that as well.

Answer 5

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

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

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

Answer 6

Searching for both F and f seems like way too much work

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

Or to quote my friend Ritchie - when searching in sql, trim it and then force it upper