CODEWITHSUNDEEP
sqlsql-server
vatspoo
vatspoo

Reputation: 401

SQL BETWEEN not working properly

I am using Microsoft SQL Server

I am trying to use the following sql command to get the records between First_Name "Nilsen" and "Ram"

select * from persons where First_Name between 'Nilsen' and 'Ram'

but i am getting the output as two records with first names "nilsen" and "ram"; not the records between these records.

In another command, i tried doing similar thing with Last names. 

select * from persons where Last_Name between 'Johan' and 'Chandra'

this command shows just a blank persons table.

Please tell me its not working properly.

Upvotes: 2

Views: 1168

Answers (3)

Quassnoi
Quassnoi

Reputation: 425251

This query:

SELECT  *
FROM    persons
WHERE   First_Name between 'Nilsen' and 'Ram'

will return all entries with First_Name alphabetically between Nilsen and Ram (like Oscar, Rachel or Norbert)

This query:

SELECT  *
FROM    persons
WHERE   Last_Name between 'Johan' and 'Chandra'

will never return anything since Johan is greater than Chandra (i. e. goes later in alphabetical order).

Update:

Just a wild guess: if you want to match something like Nilsen Hermenegild J. P. Ram, Jr., you need to use this:

SELECT  *
FROM    persons
WHERE   FirstName LIKE '%Nilsen%Ram%

Upvotes: 4

Fermin
Fermin

Reputation: 36071

This is how BETWEEN works, from MSDN

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

so

select * from persons where First_Name between 'Nilsen' and 'Ram'

should return records with first names 'Nilsen' and 'Ram', plus the records between.

Upvotes: 1

Hogan
Hogan

Reputation: 70513

Just guessing, try this:

select * from persons where lower(First_Name) between 'nilsen' and 'ram'

Upvotes: 0

Related Questions