Get all records help

Question

User1034446946 posted

Hi

I have a field in a table  which is simply for sorting and say I want to select all from "e" to "w", how do i do it?

Answer 1

User177399542 posted

Hi You can use "Between" operator:

SELECT * FROM YourTableName WHERE ColumnName BETWEEN 'e' AND 'w'

Full Example:

DECLARE @MyTable TABLE
(
FirstChar nvarchar(1),
FullName nvarchar(50)
) 

INSERT INTO @MyTable
        ( FirstChar, FullName )
VALUES  
('a','Apple'),
('b','Boy'),
('c','Cat'),
('d','Dog'),
('e','Egg'),
('f','Fish'),
('e','Elephant')

SELECT * FROM @MyTable WHERE FirstChar BETWEEN 'a' AND 'b'

Answer 2

User516094431 posted

As per you description, You can use BETWEEN or <=/>= in where clause.

SELECT * FROM TableName WHERE ColumnName BETWEEN 'e' AND 'w' 
ORDER BY ColumnName

Or you can use:

SELECT * FROM TableName WHERE ColumnName >= 'e' AND ColumnName <= 'w' 
ORDER BY ColumnName

Answer 3

User1120430333 posted

Are you talking about this in regards to using Entity Framework?

Answer 4

User1034446946 posted

Are you talking about this in regards to using Entity Framework?

Yes, sorry should have mentioned, i thought it would be a fairly simple conversion to Linq, lamda

Answer 5

User1034446946 posted

i should also ask, if I add more columns can i do a between multiple columns?

Answer 6

User475983607 posted

i should also ask, if I add more columns can i do a between multiple columns?

Sure, use that's a logical AND or logical OR.  

SELECT * 
FROM SomeTable
WHERE Col1 BETWEEN @val1 AND @val2
   AND Col2 BETWEEN @val1 AND @val2

Can you post code that shows the problem you are trying to solve?

Answer 7

User1034446946 posted

Thanks for the info, don't have code at present just didn't know where to start, will be testing it soon and if theres an issue will get back.

Thanks everyone

Answer 8

User1120430333 posted

DA924

Are you talking about this in regards to using Entity Framework?

Yes, sorry should have mentioned, i thought it would be a fairly simple conversion to Linq, lamda

Then how does T-SQL responses imply that it's a Linq solution?
You would have to be using some kind of Linq Contains() or Startswith().
You could use Entity-SQL that is like T-SQL and do what you see in the T-SQL examples.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/entity-sql-overview

Answer 9

User1034446946 posted

Then how does T-SQL responses imply that it's a Linq solution? 

It doesn't ,i needed a place to start, the responses did that.

You would have to be using some kind of Linq Contains() or Startswith().

Thats what I found out after googling, based on the responses in this thread

https://stackoverflow.com/questions/12476686/what-is-equivalent-to-clause-between-for-comparasion-strings-in-linq-or-lambda

And thanks for the link, will read it later.