CODEWITHSUNDEEP
sqlsql-serverselect
Eliseo
Eliseo

Reputation: 416

Microsoft SQL between statement for characters is not inclusive?

I am trying to select all values that have a first name beginning with the letters a-d, however when I do this

select * from tblprofile where firstname between 'a' and 'd'

I get all values from a to c, not including d, how can I make sure it includes d?

Upvotes: 1

Views: 874

Answers (3)

Julian Leite
Julian Leite

Reputation: 112

Another way is using a union select like this

SELECT * FROM tblprofile WHERE LEFT(FirstName,1) = 'a' union SELECT * FROM tblprofile WHERE LEFT(FirstName,1) = 'b' union SELECT * FROM tblprofile WHERE LEFT(FirstName,1) = 'c' union SELECT * FROM tblprofile WHERE LEFT(FirstName,1) = 'z'

The advantage of using union is if you need to get the results stating with A, K and X, strings out of sequence.

Upvotes: 0

Jakub Lortz
Jakub Lortz

Reputation: 14896

It is inclusive.

You don't get the results you want because any string beginning with 'd' and longer than 1 character is greater than 'd'. For example 'da' > 'd'. So, your query would return all values starting with 'a', 'b', 'c', and a value 'd'.

To get the results you want use

select * from tblprofile where firstname >= 'a' and firstname < 'e'

Upvotes: 7

Sergio
Sergio

Reputation: 503

Try using Left() Function:

SELECT *
FROM tblprofile
WHERE LEFT(FirstName,1) between 'a' and 'd'

Upvotes: 3

Related Questions