Sorting SQL by first two characters of fields

Question

I'm trying to sort some data by sales person initials, and the sales rep field is 3 chars long, and is Firstname, Lastname and Account type. So, Bob Smith would be BS* and I just need to sort by the first two characters.

How can I pull all data for a certain rep, where the first two characters of the field equals BS?

Answer 1

SELECT * FROM SalesRep
WHERE SUBSTRING(SalesRepID, 1, 2) = 'BS'

You didn't say what database you were using, this works in MS SQL Server.

Answer 2

What about this

SELECT * FROM SalesTable WHERE SalesRepField LIKE 'BS_'

Answer 3

You haven't said what DBMS you are using. The following would work in Oracle, and something like them in most other DBMSs

1) where sales_rep like 'BS%'

2) where substr(sales_rep,1,2) = 'BS'

Answer 4

In some databases you can actually do

select * from SalesRep order by substring(SalesRepID, 1, 2)

Othere require you to

select *, Substring(SalesRepID, 1, 2) as foo from SalesRep order by foo

And in still others, you can't do it at all (but will have to sort your output in program code after you get it from the database).

Addition: If you actually want just the data for one sales rep, do as the others suggest. Otherwise, either you want to sort by the thing or maybe group by the thing.

Answer 5

I hope that you never end up with two sales reps who happen to have the same initials.

Also, sorting and filtering are two completely different things. You talk about sorting in the question title and first paragraph, but your question is about filtering. Since you can just ORDER BY on the field and it will use the first two characters anyway, I'll give you an answer for the filtering part.

You don't mention your RDBMS, but this will work in any product:

SELECT
     my_columns
FROM
     My_Table
WHERE
     sales_rep LIKE 'BS%'

If you're using a variable/parameter then:

SELECT
     my_columns
FROM
     My_Table
WHERE
     sales_rep LIKE @my_param + '%'

You can also use:

LEFT(sales_rep, 2) = 'BS'

I would stay away from:

SUBSTRING(sales_rep, 1, 2) = 'BS'

Depending on your SQL engine, it might not be smart enough to realize that it can use an index on the last one.