Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Writing where clause has been easiest thing

  • Writing where clause has been easiest thing. Most of the time we learn this as a first thing in SQL. I have often seen that many times a simple question with where one has to write about where clause they often fail or get confused. Let us see how many different way you can write following where clause related question - How can you write a WHERE Clause to search names between A-J?

    Posted on 01-04-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

40  Answers  

Subscribe to Notifications
Previous 1 | 2 | 3 | 4 Next
  • Score
    3

    Here is the answer: With the below statement we can write a WHERE Clause to search names between A-J:

    select * From TABLE_NAME Where NAMES like '[A-J]%'

    Thanks, Deepti.

    Replied on Jan 4 2012 1:05AM  . 
    dips
    927 · 0% · 30
  • Score
    3
    --Method: 1 (Sargable) (Most preferrable to me)
    SELECT * 
    FROM T11
    WHERE name LIKE '[A-J]%'
    
    
    --Method 2:(Sargable)
    Select * From T11 Where name like 'A%' Or
    name like 'B%' Or
    name like 'C%' Or
    name like 'D%' Or
    name like 'E%' Or
    name like 'F%' Or
    name like 'G%' Or
    name like 'H%' Or
    name like 'I%' Or
    name like 'j%' 
    
    --Method: 3 (Non-Sargable)
    Select * From T11 Where CHARINDEX('a',name) = 1 or
    CHARINDEX('b',name) = 1 or
    CHARINDEX('c',name) = 1 or
    CHARINDEX('d',name) = 1 or
    CHARINDEX('e',name) = 1 or
    CHARINDEX('f',name) = 1 or
    CHARINDEX('g',name) = 1 or
    CHARINDEX('h',name) = 1 or
    CHARINDEX('i',name) = 1 or
    CHARINDEX('j',name) = 1
    
    --Method: 4 (Non-Sargable)
    Select * From T11 Where (ASCII(SUBSTRING(name,1,1)) >= 65 and ASCII(SUBSTRING(name,1,1)) <= 74) OR
    (ASCII(SUBSTRING(name,1,1)) >= 97 and ASCII(SUBSTRING(name,1,1)) <= 106)
    
    
    Replied on Jan 4 2012 1:06AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    5

    We have following options to retrieve the data which name starts with A to J.

    Option:1

    SELECT  *
    FROM    [TableName]
    WHERE   [TableName].[ColumnName] like '[A-J]%'
    

    Option: 2

    SELECT  *
    FROM    [TableName]
    WHERE   ( [TableName].[ColumnName] like 'A%'
              OR [TableName].[ColumnName] like 'B%'
              OR [TableName].[ColumnName] like 'C%'
              OR [TableName].[ColumnName] like 'D%'
              OR [TableName].[ColumnName] like 'E%'
              OR [TableName].[ColumnName] like 'F%'
              OR [TableName].[ColumnName] like 'G%'
              OR [TableName].[ColumnName] like 'H%'
              OR [TableName].[ColumnName] like 'I%'
              OR [TableName].[ColumnName] like 'J%'
            )
    

    Option:3

    SELECT  *
    FROM    [TableName]
    WHERE   ( SUBSTRING([TableName].[ColumnName], 1, 1) = 'A'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'B'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'C'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'D'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'E'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'F'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'G'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'H'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'I'
              OR SUBSTRING([TableName].[ColumnName], 1, 1) = 'J'
            )
    

    Option: 4

    SELECT  *
    FROM    [TableName]
    WHERE   ( CHARINDEX('A', [TableName].[ColumnName]) = 1
              OR CHARINDEX('B', [TableName].[ColumnName]) = 1
              OR CHARINDEX('C', [TableName].[ColumnName]) = 1
              OR CHARINDEX('D', [TableName].[ColumnName]) = 1
              OR CHARINDEX('E', [TableName].[ColumnName]) = 1
              OR CHARINDEX('F', [TableName].[ColumnName]) = 1
              OR CHARINDEX('G', [TableName].[ColumnName]) = 1
              OR CHARINDEX('H', [TableName].[ColumnName]) = 1
              OR CHARINDEX('I', [TableName].[ColumnName]) = 1
              OR CHARINDEX('J', [TableName].[ColumnName]) = 1
            )
    

    Option 4 can be done using "PATINDEX" also.

    Replied on Jan 4 2012 1:51AM  . 
    Hardik Doshi
    20 · 9% · 2853
  • Score
    3

    Query using like operator: select name from table where name like '[A-J]%'

    Query using between operator: In this case, the last character is not included in SQL Server 2005 hence instead of 'J', 'K' is taken as limit value. select name from emp_table where name between 'A' and 'K'.

    Replied on Jan 4 2012 2:35AM  . 
    manik
    752 · 0% · 42
  • Score
    6

    Method 1

    SELECT  *
    FROM    [TableName]
    WHERE   [TableName].[ColumnName] like '[A-J]%'
    

    Method 2

    SELECT  *
    FROM     [TableName]
    WHERE   SUBSTRING( [TableName].[ColumnName], 1, 1) BETWEEN 'A' AND 'J'
    

    Method 3

    SELECT  *
    FROM     [TableName]
    WHERE   'A' <=SUBSTRING( [TableName].[ColumnName], 1, 1)  AND 'J'>=SUBSTRING( [TableName].[ColumnName], 1, 1)
    

    Method 4

    SELECT  *
    FROM     [TableName]
    WHERE   'A' <=LEFT([TableName].[ColumnName], 1)  AND 'J'>=LEFT[TableName].[ColumnName], 1)
    
    Replied on Jan 4 2012 2:49AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    3

    SELECT * FROM [TableName] WHERE PATINDEX( '[A-J]%', [ColumnName] )> 0

    PATINDEX - Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

    Replied on Jan 4 2012 3:09AM  . 
    softsara
    1904 · 0% · 10
  • Score
    6

    1.

    select * 
    FROM Table_ 
    WHERE column_ like '[A-J]'
    

    2.

    select * 
    FROM Table_ 
    WHERE (ASCII(column_)>=ASCII('A') AND ASCII(column_)<=ASCII('J'))
        			OR
          (ASCII(column_)>=ASCII('a') AND ASCII(column_)<=ASCII('j'))
    

    3.

    select * 
    FROM Table_ 
    WHERE ( ASCII(column_) BETWEEN ASCII('A') AND ASCII('J'))
        			OR
          (ASCII(column_) BETWEEN ASCII('a') AND ASCII('j'))
    

    4.

    select * 
    FROM Table_  
    WHERE ( column_ BETWEEN 'A' AND 'j')
    

    5.

    select * 
    FROM Table_  
    WHERE  column_>='A' and  column_ <= 'J'
    
    Replied on Jan 4 2012 3:35AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    3

    select * From TABLE_NAME Where NAMES like '%A%'

    UNION

    select * From TABLE_NAME Where NAMES like '%B%'

    UNION

    select * From TABLE_NAME Where NAMES like '%C%'

    UNION

    select * From TABLE_NAME Where NAMES like '%D%'

    UNION

    select * From TABLE_NAME Where NAMES like '%E%'

    UNION

    select * From TABLE_NAME Where NAMES like '%F%'

    UNION

    select * From TABLE_NAME Where NAMES like '%G%'

    UNION

    select * From TABLE_NAME Where NAMES like '%H%'

    UNION

    select * From TABLE_NAME Where NAMES like '%I%'

    UNION

    select * From TABLE_NAME Where NAMES like '%J%'

    Replied on Jan 4 2012 3:44AM  . 
    ajiteshmalhotra
    1767 · 0% · 11
  • Score
    5

    select * from TABLENAME where COLUMNNAME like '[A-J]%'

    Replied on Jan 4 2012 5:36AM  . 
    kohila2001
    2363 · 0% · 5
  • Score
    6

    Option 1

    SELECT  ColumnName 
    FROM    TableName
    WHERE   ColumnName like '[A-J]%'
    

    --This will include even charcters like 'À','Ç','È', Select char(201) etc .....


    Option 2

    SELECT * 
    FROM TableName 
    WHERE PATINDEX( '[A-J]%', ColumnName)> 0
    


    Option 3

    SELECT  *
    FROM     TableName
    WHERE   'A' <=LEFT(LastName, 1)  AND 'J'>=LEFT(LastName, 1)
    

    In this option we can use functions like CHARINDEX,SUBSTRING,Right etc...


    Option 4

    By Individual likes.. Even this will not include charcters like 'À','Ç',Select char(200)....

    SELECT ColumnName  
    
    FROM  TableName
    
    WHERE ColumnName Like 'A%'
    
    Or ColumnName Like 'B%'
    
    Or ColumnName Like 'C%'
    
    Or ColumnName Like 'D%'
    
    Or ColumnName Like 'E%'
    
    Or ColumnName Like 'F%'
    
    Or ColumnName Like 'G%'
    
    Or ColumnName Like 'H%'
    
    Or ColumnName Like 'I%'
    
    Or ColumnName Like 'J%'
    


    Option 5

    --This will not include charcters like 'À','Ç',Select char(200)....

    SELECT  ColumnName 
    FROM    TableName
    WHERE   ColumnNamelike '[ABCDEFGHIJ]%'
    

    Or

    SELECT  ColumnName 
    FROM    TableName
    WHERE   ColumnNamelike '[A-BCDEFGHIJ]%'
    


    Option 6

    For Full text index query

    select * from table where contains (columname,'"A*"')
    

    Option 6 Refrence Click

    Thanks Yogesh

    Replied on Jan 4 2012 6:57AM  . 
    Yogesh Kamble
    142 · 1% · 349
Previous 1 | 2 | 3 | 4 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.