locked
Get all records help RRS feed

  • 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?

    Monday, March 19, 2018 1:16 AM

Answers

  • 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'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 4:49 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 5:15 AM
  • User1120430333 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 9:07 AM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 2:03 PM

All replies

  • 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'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 4:49 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 5:15 AM
  • User1120430333 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 9:07 AM
  • 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

    Monday, March 19, 2018 1:55 PM
  • User1034446946 posted

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

    Monday, March 19, 2018 1:58 PM
  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 19, 2018 2:03 PM
  • 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

    Monday, March 19, 2018 2:27 PM
  • 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

    Monday, March 19, 2018 3:45 PM
  • 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.

    Monday, March 19, 2018 4:10 PM