Sort SQL by first two characters of fields

Question

I'm trying to sort some data by the initials of sellers, and the sales field is 3 characters, and this is the name, surname and type of account. So Bob Smith will be BS *, and I just need to sort the first two characters.

How can I get all the data for a specific reputation where the first two characters of the field are BS?

Answer 1

In some databases you can really do

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

Required of you

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

And in others, you cannot do this at all (but you will have to sort your output in program code after receiving it from the database).

Addition: if you really want only data for one sales representative, do what others offer. Otherwise, either you want sort by thing, or perhaps group by thing.

Answer 2

How about this

 SELECT * FROM SalesTable WHERE SalesRepField LIKE 'BS_' 

Answer 3

You did not say which DBMS you are using. The following will work in Oracle, and something similar in most other DBMSs

1) where sales_rep is like 'BS%'

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

Answer 4

I hope you never have two sales representatives who have the same initials.

In addition, sorting and filtering are two completely different things. You are talking about sorting in the title of the question and the first paragraph, but your question is about filtering. Since you can simply ORDER BY in the field, and in any case it will use the first two characters, I will give you an answer to the filtering part.

You do not mention your RDBMS, but this will work in any product:

 SELECT my_columns FROM My_Table WHERE sales_rep LIKE 'BS%' 

If you use 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 may not be smart enough to realize that it can use the index on the latter.

Answer 5

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

You did not say which database you used, it works in MS SQL Server.