SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.

Wildcard ranges

If you have ever been to a convention where they have a morning registration desk that must handle thousands of people in a short time you know they must put some pre-planning thought into how to handle this burst of volume. In fact often they will have many registration desks running in parallel to make things run faster. The first registration desk might handle all customer last names starting from A to K. Desk 2 will handle names from L to Q and the third desk will handle from R to Z.

With my last name being Morelan I would naturally head to registration desk #2 knowing that desk has the list with my name on it and the other desks don’t. Now let’s say you are in charge of creating these three separate lists and sending them out to the right registration workers. You know how to sort but how can you separate this lists using wildcards?

Wildcard Basics Recap

Lets start off with something most of us know already. Most SQL folks understand the usefulness and power of the basic uses of wildcards. Using wildcards allows you to do pattern matches in a column. In this case our criteria does not want to use the = sign to find a pattern match. The operator that allows you to do approximate predicates is LIKE. The LIKE operator allows you to do special relative searches to filter your result set.

--Find all LastNames that start with the letter A
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'

To find everyone whose last name starts with the letter B, you need “B” to be the first letter. After the letter B you can have any number of characters. Using B% in single quotes after the LIKE operator gets all last names starting with the letter B.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'B%'

Wildcard ranges or set specifiers

If you want to find all LastName values starting with the letters A or B you can use two predicates in your WHERE clause. You need to separate them with the OR operator.

--Find all LastNames that start with the letter B
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'

Finding names beginning with A or B is easy. How about the registration desk example where want the names ranging from A-K? This works well until you want a range of A-K as in the example below:

--Find all LastNames ranging from A-K
SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
OR LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'E%'
OR LastName LIKE 'F%'
OR LastName LIKE 'G%'
OR LastName LIKE 'H%'
OR LastName LIKE 'I%'
OR LastName LIKE 'J%'
OR LastName LIKE 'K%'

The previous query does find LastName values starting from A-K. However, if you need a range of letters, the LIKE operator has many better options. We only really care about the first letter of the last name and there a several first letters that fit with what were looking for. The first letter of the last name can be A,B,C,D,E,F,G,H,I,J or K. Simply list all the choices you want for the first letter inside a set of square brackets.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[ABCDEFGHIJK]%'

Square brackets with wildcards enclose ranges or sets for 1 position. In this case the first position is a set of 11 different possible letters. This is not a series of letter but a multiple choice of letters. For example this works regardless of the order you put your letters in. This code sample below does the exact same thing.

--LastNames ranging from A to K using a set of 11 letters
SELECT *
FROM Employee
WHERE LastName LIKE '[KBCDEFGHIJA]%'

Again the set is how many letters you put in the square brackets. The code below is a logical mistake where you won’t get A to K but you just get A or K for the first letter.

--Find all LastNames starting with A or K (Mistake
SELECT *
FROM Employee
WHERE LastName LIKE '[AK]%'

Since we’re looking for the first letter to be within a range  from A to K, we specify that range in square brackets. This is even easier than using a set. The wildcard after the brackets allows any number of characters after the range.

--LastNames ranging from A to K using a range
SELECT *
FROM Employee
WHERE LastName LIKE '[A-K]%'

Note: this range will not work if your LIKE was changed to an equal (=) sign. The following code will not return any records to your result set:

--Bad query (it won’t error but returns no records)
SELECT *
FROM Employee
WHERE LastName = '[A-K]%'

Note: If you want to setup the sample JProCo database on your system you can watch this video.

Question 1

Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

  1. SELECT * FROM Customer
    WHERE Firstname <= ‘m%’
  2. SELECT * FROM Customer
    WHERE Firstname  = ‘a-m%’
  3. SELECT * FROM Customer
    WHERE Firstname like ‘a-m%’
  4. SELECT * FROM Customer
    WHERE Firstname  = ‘[a-m]% ‘
  5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

Please post your answer in comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

337 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

  1. If we need to find all first names that start with the letters A-M (i.e A to M) in your Customer table.

    then query should be as:

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Varinder Sandhu from India

  2. Correct Answer is : Option 5

    explanation : using wildcard character ‘-‘ , we can query over a range. If we use = instead of like we will get no eroor but also no result.

    Country : India [Pune]

  3. The answer is option 5
    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Since we are searching for the range of character we need to use like operator. As we are searching for the first name that starts with a to m we need to use [a-m] and followed by ‘%’


    Sunitha
    Hyderabad,India

  4. I would use #5 as it creates the A-M set with the wildcard on the outside of the set. Using the LIKE statement is mandatory to enable the use of the “%” wildcard. I am located in the United States. If I were in a database that enforces case sensitivity, I would then use “like ‘[A-M]%'” to match your question.

  5. I am living in India

    Answer number 5 is correct :

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    We have use the square bracket and put ‘A’ and ‘M’ in it with ‘-‘ which shows a range. All the name staring with ‘A’ to ‘M’ range will be display in the final output.

  6. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’‘

    Option 5 is the correct answer

    because its a range from a-m and we need to use Square brackets and as it can starts with a-m, we must use Like operator.

    Country of Resident – INDIA

  7. Answer is option 5:
    SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    because wildcard characters [] returns the data with in the specified range that starts with the letters a-m

    Country of Residence – INDIA.

  8. Hi the correct answer is option 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    this query will return all records from Customer table whose firstname starts with a or b or……..or m. Case dosent matter.

    Sumit Kumar Gaud
    India

  9. ans is option 5
    because using like only wildcard checking possible
    in option 3 not properly where condition so, 5 is answer

    • Sorry Forgot to mention.

      The reason is that :

      1 st option will not work as it is not integer comparison.

      other options return names starting with “a-m” and not from range.

      The forth option will not return any results as mention in post range will not work with “=” but like.

      I am from India

  10. 5) SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’
    The above is the correct answer.
    The LIKE operator is used & the correct syntax for range selection is used here.
    Option 1 does not work bcos it displays names starting with A-L range omitting M.
    Country – Bangalore,India

  11. This is Ritesh from India.

    I would consider 5th query as best choice which is as per below.

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Actually the search we are looking for, could be done by two queries from the list which is 1st and 5th. remaining queries wouldn’t return desired result.

    Actually if you have only alphabetic value in your column and if you are using any of the two query below, would return result.

    SELECT * FROM Customer
    WHERE Firstname <= ‘m%’

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    but the problem with "<='m%'" is, it will compare ASCII and anything less that the ASCII of m would come in the list, obviously, it will return range a,b,c……n,m

    but not only this, any data which start with numeric value or space, even come in the result set that is why I would like to go for [a-m]% with LIKE operator.

  12. The correct Answer:
    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Acoording to me it is correct b’coz we are filtering for the FirstName which start with SPECIFIC character range (i.e. a-m). And we are only checking for the first character, so we should use [ ].

    Hey, I am very new to SQL Server, so please forgive me if any silly explanation is there.

    Country : India

  13. My answer is option 5. as said above we can specify the range in [ ] with like operator. so option 5 will work. option 1 shows upto ‘L’ but not ‘M’ I am from India,

  14. Option 5 is the correct answer.

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Returns all the record that starts with range of character specified in charlist [a-m] (i.e)[abcdefghijklm] from the Customer table.

    by
    Yasodha.N (India)

  15. To find all first names that start with the letters A-M in Customer table. Which we will use below SQL code:

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  16. To retrieve names starting with A – M from Customer table you would need to use: Option 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Using a char list wildcard in a square bracket ensures that the query returns any single character in range of char list (in this case names starting with ‘A’ through ‘M’)

    Using a ‘<=' and '=' instead of LIKE would not work so the Option 1 , 2 & 4 is out of question.

    Option 3 does not return results as its missing square brackets with range of choices.

  17. Option 5 is the correct answer.

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Returns all the record that starts with range of character specified in charlist [a-m] (i.e)[abcdefghijklm] from the Customer table.

    by
    Yasodha.N (India)

  18. 1st comment is why do you not include Europe in your quiz?

    2nd comment should you choose to include anyone from England :-) this is the answer

    5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Regards

    Nigel

  19. Example # 5 will return the required data. It used the LIKE options that allows for relative searches. The Brackets allow for the range of the realtive search. And of course the WILDCARD allows for a Pattern match.

    Also I realy like this kind of Q&A. Good Job!

    Ron

  20. The answer will be;

    SELECT * FROM tblStudentMaster
    WHERE Firstname like ‘[a-m]%’ order by firstName

    But i little confused in Option 1 and 5. 1st option will return single value of name starts with M.

    Will you tell me the difference between 1st and 5th option.

  21. Pinal,

    The correct answer for today’s quiz is the 5th choice:

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    I am from the United States.

    Best Regards,

    Bill Pepping

  22. I believe the answer would be: 5) SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    However, is it not case sensitive? Based on your question, should it not be SELECT * FROM Customer
    WHERE Firstname like ‘[A-M]%’ ?

    The reason is that you need to use the like operator, rather than the equal sign, when doing a wildcard search. In addition, the range needs to be in square brackets in order to work correctly on the first character of the search.

    I am from the United States (Ohio).

  23. After reading your post, obviously the answer is #5, because you made this topic super clear in your blog. Thanks a lot.

  24. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

  25. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  26. Great idea for a series. I remember you mentioning Joes 2 Pros a while back and how good it was.

    The correct choice would be #5.

    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    It is the only one that has includes the square brackets and the like condition.

    In the USA.

  27. Both 1 & 5 will work to find fist character. But to find out character on second or third place option 1st didn’t work. Because “<=" is comparison operator & "Like" is Logical operator.

    COUNTRY:- INDIA

  28. 5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    The LIKE looks for words with those letters in them. The brackets will show those range of names that begin with those letters. The wildcard after the brackets within the single quotes will search for every name that begins with the letters in the brackets.

    Sam Masterson – USA

  29. Select number five is the one which gets the desired result.
    Why? Since the range from a-m in square brackets is a multiple choice of those letters if combined with the like operator. The wildcard after the square brackets allows any number of characters after the range.

    I’m from germany! But why do you include only America and India as possible winners? :-(

    By the way: excellent post. The wildcard range was new to me. Thanks for sharing you knowledge!!

    Greetings,
    Michael Mikic

  30. Sorry Late but with right answer.
    Answer 5 is right.
    (because in your explanation you have already mention that like will not work with = the ans 1,2 and 4 out now come on 3 and 5 in three declaration of like words is wrong. so answer 5 is correct)
    below code from your tutorials also:

    –LastNames ranging from A to K using a range
    SELECT *
    FROM Employee
    WHERE LastName LIKE ‘[A-K]%’
    similar with question..

    Thanks for giving the chance of Learn and earn(win).

  31. 5. But I’m in .UK, so don’t qualify.

    It’s a useful thing to know, and to be reminded of, so thanks for that!

  32. The correct answer is the no 5.

    It will display all FirstName that start from letter A to letter M.

    The answer no 1 it will display all FirstName starting with letter A to letter M but not all names greather than char % i.e. FirstName = Mike won’t be displayed by the query no 1.

    Regards,
    Antonio
    Spain

  33. Option 1: The <= operator won't work on text expressions: http://msdn.microsoft.com/en-us/library/ms188074.aspx

    Option 2: returns all rows, if any, where the Firstname is exactly a-m%
    Option 3: returns all rows, if any, where the Firstname is begins with a?m, where ? can be any single character:

    http://msdn.microsoft.com/en-us/library/ms179859.aspx

    Option 4: returns all rows, if any, where the Firstname is exactly [a-m]%
    Option 5: returns all rows, if any, where the Firstname is begins with any letter in the range a-m, case sensitive

    The [] is used for range, % is used for wildcards and must be used with the LIKE Operator.

    So, none of the options is right.

    Try:

    SELECT * FROM Customer
    WHERE FirstName like ‘[A-M]%’

    if you want names that is case insensitive, try:

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’ or FirstName like ‘[A-M]%’

    Country: Australia

  34. 5. SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    Uses a specified range, in this case A through M, for the first letter (wildcard suffix) from the FirstName column.

    USA

  35. Pinal,

    Option : 5 is correct.

    Option:1 -> wrong. We need only name which starts A-M but this query will fetch even if there are any name which starts with special characters (*,-)
    Option:2 -> Wrong. We should not use “=” operator
    Option:3 -> Wrong. This query will exactly fetch only when the name start with ‘a-m’ (example if the first name is “a-master”)
    Option:4 -> Wrong. We should not use “=” operator
    Option:5 -> Correct. It will fetch all the names which start with the character between A to M

    Country : USA (Chicago)

  36. Option 1: This option will give values till M. That is starting from A to M and after that it truncates the name which has length more than one letter.
    ex : we will get till M but not M. (or) Matt (or) Ma etc so this option Fails even if we specify
    SELECT * FROM Customer WHERE Firstname <= ‘n%’ this will satisfy our required condition but will also give extra records which has First name starting to correct 1 place i.e. N but not n. (or) Naa etc so this option is wrong.
    Yes we cannot apply expression on to letters or words but here its taking ASCII values and matching there by.

    Here, Empty and empty space will be selected because by default it comes from the start but 'NULL' values will not come here.One more reason why this option is wrong.

    Option 2: and Option 3: returns Nothing so wrong answer.

    Option 4:Bad query (it won’t error but returns no records) as explained by you wrong answer.

    Option 5: The LIKE operator looks for words with those letters in the range. The brackets will show those range of names that begin with those letters. The wildcard after the brackets within the single quotes will search for every name that begins with the letters in the brackets.
    Here no nulls and no empty results turns up so the Right option is Option 5:

    Many thanks for helping all of us work together.
    i am looking forward to many more like this.

    Country: India

  37. Should you decide to include Australia in your prize pool :)

    The correct answer is #5.

    Option 1 is almost correct. It will find everybody who’s name starts with A-L, however the <= is restricting the name to be less that M… so M[arty] for example will not be returned. This could be modified to < 'n%' to get a correct result.

    Option 2 is incorrect, it is looking for a string literal a-m.

    Option 3 is also incorrect as it is also looking for a string literal a-m

    Option 4 is incorrect as it will return rows matching [a-m]% exactly not those like it

  38. The right answer is

    #5 – SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    Because like in Regular Expressions the metacharacters [] means a class of characters to be searched, in this case a class of character range from a through m [a-m] mixed with the wildcard character %, so the whole expression can be explained like:

    Find all rows in the customers table where the values in the Firstname field begins with any character from a through m.

    That´s the way i understand the #5 is tye right answer.

    Country: México

  39. Answer is
    5. SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’
    All other options doesnt use “like” notation, which will not recognise the special string format.

    Country: Australia

  40. Question 1

    Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    Answer is
    5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  41. Correct answer is option 5.

    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Cheers,
    Prasad
    from INDIA (PUNE)

  42. The correct answer is option 5

    Explanation:
    LIKE operator is only used for wildcard search and not =
    [A-M] is a SET based search for all names starting with A-M

    Sakthi
    India

  43. Answer is Option 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Returns all the record that starts with range of character specified in charlist [a-m] from the Customer table.

    Country : INDIA

  44. Answer is : Option 5: “SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’”

    Explanation of the Answer is :
    This option only returns all the Firstname starting from ‘a’ to ‘m’ as here we have defined range of [a-m]. Also as we’re looking for the first letter to be within a range from a to m, we need specify that range in square brackets with LIKE. So, only option 5 is valid for this answer.

    Country : India [Noida]

  45. The correct answer is option:5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Becasuse ‘Like’ Query Have functionality when You Put any character like
    in this format e.x. ‘[0-9]%’ or ‘[A-z]‘

    It Will Return Result With Start Chatracter between 0 to 9

    So You Have ‘First Name’ In Customer Column = ‘chirag’,’mohit’,’ozo’

    so the Result of this query SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’ = chirag,Mohit only

    becasue ‘ozo’ First Name not between a to m character

  46. Question 1

    Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    Answer is
    5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Because names that should be starts with letters a to m in alphabets
    and ‘[' ']‘ square braces indicates any one of the characters mentioned in the braces

    multiple case in representing [] square braces
    1.If you want to mention range of characters starts with characters or numbers then
    ‘[0-9]%’ or ‘[a-z]%’
    -considers word starts with 0,1,2,3…9 or a,b,c….y,z these characters
    -single quote before and after the braces represents characters

    2.If you want to specify random characters you need to specify as below
    ‘[a,x,z]%’

    And %-indicates any characters after the characters specifies in the square braces.

    Country -India

    Thank You :-)

  47. Question 1

    Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    Answer is
    5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Because names that should be starts with letters a to m in alphabets
    and ‘[' ']‘ square braces indicates any one of the characters mentioned in the braces

    multiple case in representing [] square braces
    1.If you want to mention range of characters starts with characters or numbers then
    ‘[0-9]%’ or ‘[a-z]%’
    -considers word starts with 0,1,2,3…9 or a,b,c….y,z these characters
    -single quote before and after the braces represents characters

    2.If you want to specify random characters you need to specify as below
    ‘[a,x,z]%’

    And %-indicates any characters after the characters specified in the square braces.

    Country -India

    Thank You :-)

  48. Hi All,

    Thank you for excellent participation. The correct answer is already posted on Fan Page. The winner will be posted tomorrow at the same page.

    Many thanks for participating. The new question for today is already posted here. I encourage all of you to take part in the same. You can participate every day even though you have won once.

    The person who participate all the 35 day will get surprise gift from me.

    Kind Regards,
    Pinal Dave

  49. as the question is to find all the firstname ranging from a to m (starting with all char a to m) hence the answer is # 5
    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Country – INDIA

  50. The answer will be;

    SELECT * FROM tblStudentMaster
    WHERE Firstname like ‘[a-m]%’ order by firstName

    But i little confused in Option 1 and 5. 1st option will return single result of name starts with M.

    Will you tell me the difference between 1st and 5th option.
    Or how i can define the exact difference between 1st and 5th option.

    Please send this answer me on my email address:
    [email removed]

  51. SELECT * FROM Customer
    WHERE Firstname <= ‘m%’

    and

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    both will answer but

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    is best to use because Firstname <= ‘m%’ will return blank too..

    From : INDIA

  52. OPTION : 5 IS THE CORRECT ANSWER

    Country : India

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  53. The correct option is 5 ) SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’
    Because we need to fetch information for a range from A to M hence we used [a-m] with wild card % and as we need to look for First name only hence the keyword Firstname is used.

    Country:- INDIA

  54. OPTION: Answer 5 is the correct option.

    Country: India

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  55. Rama Krishna, Hyderabad , India

    Answer 5 is correct if we want to get the data that starts with ‘A’ to ‘M’

    If we want the data only letters with ‘A’ and ‘M’

    SELECT * FROM Customer WHERE Firstname like ‘[a&M]%’

  56. the correct answer is “answer 5″
    SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    iT will return all record of range “a to m”.
    i am from india

  57. Hi Pinal,

    Option 5 is correct answer,the reason is that as option 1,2 and 4th doesnt obey pattern matching and remaining is 3rd and 5th, but 5th optin has included within the braces so it works finee

  58. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’‘

    Option 5 is the correct answer

    Country of Resident – INDIA

  59. 1. DISCARDED
    2,3,4. = will find exact match instead of pattern match => DISCARDED
    5. In Square Bracket, range is provided from A to M and afterwards % sign is given. Hence using LIKE operator it will result all the records having first letter between a and m.

    Thus, 5 is the correct answer.

    India

  60. Option 4

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    It is Giving the range ([a-m]in Like operator and expecting that a and A,m And M are same

  61. Question 2:

    Q 2.) You want to find all grant names that have and Underscore as the second letter. Which SQL code would you use?
    Answer is
    1. SELECT * FROM [Grant]
    WHERE GrantName like ‘_[_]% ‘

    Because ‘_[_]% ‘ represents

    _ —> Represents first character of the word that may be any character

    [_] —> ‘_’ character in ‘['']‘ square braces represents looking for this
    character the square brace will nullify(ignores) its(special character)
    meaning.

    % —> any number of character after [_]

    Country-India

  62. The answer is number 5 (SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’) because you must use the range [a "through" m] to get the right letters and then the wildcard after to include the rest of the name, and you must use LIKE with the wildcard.
    USA

  63. Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?
    Answer:::
    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’
    Explanation:
    Any single character within the specified range ([a-f]) or set ([abcdef]).This like between “a” and “f”

  64. Option No.: 5 is Correct Answer
    SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    Country :- India,Ahmedabad

  65. SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’ is certainly the correct answer. Thank you, once again, Pinal Dave. You are the Go-To-Guy for and fun and informative SQL information!

    –Myra Rosa
    –San Diego, CA

  66. Option No. 5 is the correct one

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Thanks

    Rene Castro (El Salvador)

  67. Correct Option: 5 (i.e. SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’ )

    Explanation : As we are searching for the range of character we need to use like operator and as we are searching for the first name that starts with ‘a’ to ‘m’ we need to use [a-m] and followed by ‘%’

    Country of Resident: India

  68. The correct answer is Option 5.
    As we want to find all first names that start with the letters A-M so ‘[A-M]%’ will give any letter Starting from A to M and followed by any other letters.

    I am From
    INDIA (AHMEDABAD)

  69. Hi pinal sir,
    The correct answer for this question is option no. 5:

    ” SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’ “.

    Reasons: This is correct answer bcoz we have to list all customer’s first name starting with A-M.
    So we have used correct column name i.e Firstname, correct sql keyword i.e. “like” for multiple pattern and correct matching parameter i.e. [] character class with – for range option with % symbol.

    Following options are not correct because:
    For option 1:
    SELECT * FROM Customer WHERE Firstname <= ‘m%’
    Like keyword is missing, range option (-) is missing , no use of character class.

    For option 2:
    SELECT * FROM Customer WHERE Firstname = ‘a-m%’
    Like keyword and Character class[] is missing.

    For option 3:
    SELECT * FROM Customer WHERE Firstname like ‘a-m%’
    Character class[] is missing.

    For option 4:
    SELECT * FROM Customer WHERE Firstname = ‘[a-m]% ‘
    Like keyword is missing.

    Thanks
    Chirag Satasiya

    • Hi pinal sir,
      The correct answer for this question is option no. 5:

      ” SELECT * FROM Customer
      WHERE Firstname like ‘[a-m]%’ “.

      Reasons: This is correct answer bcoz we have to list all customer’s first name starting with A-M.
      So we have used correct column name i.e Firstname, correct sql keyword i.e. “like” for multiple pattern and correct matching parameter i.e. [] character class with – for range option with % symbol.

      Following options are not correct because:
      For option 1:
      SELECT * FROM Customer WHERE Firstname <= ‘m%’
      Like keyword is missing, range option (-) is missing , no use of character class.

      For option 2:
      SELECT * FROM Customer WHERE Firstname = ‘a-m%’
      Like keyword and Character class[] is missing.

      For option 3:
      SELECT * FROM Customer WHERE Firstname like ‘a-m%’
      Character class[] is missing.

      For option 4:
      SELECT * FROM Customer WHERE Firstname = ‘[a-m]% ‘
      Like keyword is missing.

      Thanks
      Chirag Satasiya(Mumbai – India)

  70. Question 1

    Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    Answer:

    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Answerer: Sivaprasad S
    Country: INDIA

  71. Q 1) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

    Wildcards (%) only work with the LIKE keyword so (1), (2) and (4) are all incorrect. LIKE ‘a-m%’ would only match strings where the first three characters are ‘a-m’ so (3) is also wrong. The correct answer is (5) because the predicate uses the LIKE keyword and ends in %, meaning zero or more characters following the first character, which has its range defined correctly with [a-m].

    Winner from USA: arsqldba

    Winner from India: dilipkumarjena

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

  72. Sorry I missed the country.
    The correct answer is #5(same as my previous reply)

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Country – US

  73. Option 5 is the answer as we need to get range the alphabets should be in brackets and % at the end as we want name that start with those alphabets and we should use like for pattern matching.

    USA

  74. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    This is the best query for the required result as it used the operator – to specify the range of alphabets which can be the ones with which the First Name in the Customer table starts with.

    Soumya
    India

  75. I assume there is no case sensitivity. with that answer is #5.

    SELECT * FROM Customer
    WHERE Firstname like ‘[A-M]%’

    Since we’re looking for the first letter to be within a range from A to M, we specify that range in square brackets. The wildcard after the brackets allows any number of characters after the range.

    -Priti Shah
    USA.

  76. Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    In the question, you mentioned that first names that start with the letters A-M. But not range from. So i am assuming that you are looking for all first names with starting A-M.
    then my option3 is right

    Though i know that no person’s firstname starts with A-M but only range from A-M.

    else

    option 5 if you are looking for range of values from A-M.

    I live in United States. I dont work in this field but interested to learn.

    5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  77. it is hardly 5PM here in USA and your contest is closed. Also, what is the point of posting all the answers visible which is very easy to figure out the correct or it is just a fun to see how many people participate and log into your page.

    • Hi Zack, The contest was closed after 24 hours of the blog post. The answers are visible the goal is to learn from the answer. It is not the test about your knowledge – it is just checkmark for learning.

  78. Answer is option 5

    My Name: J M Arun

    Country: India

    Explaination:
    This range will not work if your LIKE was changed to an equal (=) sign. The following code will not return any records to your result set:

  79. Answer is option number 5. Actually I use this logic to eliminate or filter values in my where statements.

    Ex: select order_number from table where not(upper(order_number)) like ‘[A-Z]%’.

    Why? Because we have test order numbers in our data base that look like this: 0004217afv or 000345TEST. Real orders are numeric only.

  80. In earlier comment, I missed out the explanation and mentioned home country, not the country of residence :-)

    Answer: Option#5
    Explanation: This option has the LIKE operator and also puts the range in square brackets.
    Country: USA

  81. Correct Answer Of Question 1 Is:

    select FirstName from Customer where FirstName like ‘[a-m]%’

    Explanation: This query retrieve Only those Firstname which starts with ‘a to m ‘ Letters From Customer Table.

    Country: INDIA

  82. Correct Answer Of Question 2 Is:

    select GrantName from Grant where GrantName like ‘_[_]%’

    Explanation: This query retrieve Only those GrantName which belongs to ‘_'(UnderScore) as 2nd Letters From Grant Table.

  83. Correct Answer Of Question 2 Is:

    select GrantName from Grant where GrantName like ‘_[_]%’

    Explanation: This query retrieve Only those GrantName which belongs to ‘_’(UnderScore) as 2nd Letters From Grant Table.

    Country: India

  84. Correct Answer Of Question 3 Is:

    select FirstName from Customer where Name like ‘_%['']%’

    Explanation: This query retrieve Only those Firstname which belongs apostasy Letters From Customer Table any where Position.

    Country: India

  85. Hello,

    3.SELECT * FROM Customer
    WHERE Firstname like ‘a-m%’ is the correct Answer

    Reason :
    in Query not mention A to M just define “start with the letters A-M”

  86. Answer : Option 5

    Name : Abhishek Bhat

    Country: India

    Reason :
    This range will not work ‘=’ sign and for range selection from a to m [a-m] is the right syntax

  87. Hi Pinal Dave!

    This is Hari from Chennai, India. Excellent way of learning. Really thanks for your effort.

    Answer:
    ———
    SELECT * FROM Customer WHERE Firstname LIKE ‘[a-m]%’

    Place: Chennai, India

    Thanks:)

  88. hi dave,

    Answer is 5

    Explanation: Because this query retrieves only those Firstnames from customers table which starts from ‘a to m ‘ Letters.

    From
    India

    Thanks
    :-)

  89. Hi Pinal,

    This is good way to learn new things. I also leart from this.

    Answer is:
    SELECT * FROM Customer
    WHERE Firstname like ‘a-m%’

    Regards,
    Chander Sharma

  90. The answer is #5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Since we’re looking for the first letter to be within a range from A to M, we specify that range in square brackets. The wildcard after the brackets allows any number of characters after the range.

    Country of Residence: USA

  91. The answer is #5.

    As we are looking for the range from a to m for first name, as per your explanation the LIKE clause with range ‘[a-m]%’ will work.

    Syed Murtuza from USA.

  92. Answer: #5 SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    You need the [a-m] (assuming case insensitive) first (no % sign before) to indicate the acceptable range of letters for the first character. The % follows to indicate any number of characters may follow. Like is needed no “=” because we are looking to match the expression not he exact string.

    Deb – USA
    (better to post late than never)

  93. To retrieve names starting with A – M from Customer table you would need to use: Option 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  94. I am jumping in late. Option 5 is the correct answer. Firstname starting with any character a-m and ending with anything else (wild)

    Uday
    (USA)

  95. The correct option is 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    since we need to fetch information for a range from A to M hence we used [a-m] with wild card % and as we need to look for First name only hence the keyword Firstname is used.

    Country:- INDIA

  96. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  97. If we need to find all first names that start with the letters A-M (i.e A to M) in your Customer table.

    then query should be as:

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Srinivas.k (skc) from India (Hyderabad)

  98. Answer option : 5

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Explanation:

    [ ] denotes the wild card character, which shows any single character. ‘- ‘ denotes the range .

    (i) Hence, records where First name column any single character which start with a to m or A to M will be selected and displayed

    (ii) Even the the records first name where it’s just found single charachter a – m will also be displayed as % is for “Any string of zero or more characters”.

    Regards,
    Thambi
    India

  99. hi
    i have created sqllite database(question) and i save this my desktop. now i want to access(question) using html5. pls help me i am new in html5 its urgent.

    thanks in advance

  100. Q. 1) You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

    The correct answer is

    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

  101. My Answer: (option 5)
    5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Explanation
    *Without the “where” clause you will get all the records in the Customer table
    *The “where” is used as a filter
    *In option one, the syntax is incorrect; the operator “<=" can be used for <= 'm'
    but not accompanied by "%"; you need "Like" for a wildcard.
    * Option 2 has an incorrect syntax as well; you need "like" when using a wildcard and you need the brackets for a range
    *Option 3 will give you an empty set, because there is no names that starts with 'a-m'
    *Option 4 is an incorrect syntax; though the bracket is used, you can't use the "=" operator with a wildcard.
    *Option 5 is the only one that answers the question

    Country of Resident: USA

    If I am selected for a free book; I don't have volumes 4 & 5. Thanks.

  102. answer is #3
    select * from customer
    where firstname like ‘a-m%’

    xplanation:- if we want to search specific pattern matching in the database then we use the like operator ……..here we have to find the first name that start with a-m so.. we have to place a-m before the % sign it will return a first name column that start with’ A-M’

    i’m atul singh
    Country of Resident: india(mumbai)

  103. Correct Answer is 5 –
    SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    Explanation: This command will select all the Record from Customer table whose firstname start between a to m latters.

  104. The correct answer is 5 – SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Explanation :
    Because strings in SQL SERVER are compared using LIKE operator and [A-M] denotes all the alphabets between A and M .

    Regards,
    Rajesh Varma ,
    India

  105. Option 5 is correct answer

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    I Just finish the first page and i like it very much
    am highly interested.
    After Completed the first page i realize that i have to study a lot

  106. Hello,

    Answer is 5 – SELECT * FROM Customer WHERE Firstname like ‘[a-m]%’

    Explanation: ‘[-]%’ (provide specify range in square brackets)

    India
    Geetanjali Agarwal

  107. The correct answer is 5#

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    ShashiKanth Pusa
    United States

  108. the correct Answer is 5th Query
    SELECT * FROM member
    WHERE membername like ‘[a-m]%’ is the correct answer

    Senthil Kumar From india

  109. Option 5 is correct i.e.
    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    It will fetch the first name starting with letters a-m. [a-m] means it will restrict for all letters between a to m and % will give the freedom of any thing can be used as suffix after first letter being a-m. Like will help to fetch the names those are having first letters between a to m followed by any suffix.

  110. Option 5 is correct

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    the use of wildcard [charlist] will select any single character in charlist then % for any substitute for zero or any characters.

    Luanne Pang, Philippines

  111. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Since we’re looking for the first letter to be within a range from A to m, we specify that range in square brackets.
    The wildcard after the brackets allows any number of characters after the range.

  112. 5.SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    Since we’re looking for the first letter to be within a range from A to m , we specify that range in square brackets.
    The wildcard after the brackets allows any number of characters after the range.

  113. option 5 is the correct answer.
    Explanation :- here the range is specified ie from a-m. so, we have to use square brackets for ranges. so the starting letter be anywhere from a to m.

  114. option 5 is the correct answer.
    Explanation :- here the range is specified ie from a-m. so, we have to use square brackets for ranges. so the starting letter be anywhere from a to m.

    jagan mohan
    Hyderabad,India

  115. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority

  116. DECLARE @MyString VARCHAR(100)
    SET @MyString = ‘arnetonsberghosøvarnanoü’

    IF (@MyString LIKE ‘%[^a-z]%’)
    PRINT ‘Contains “special” characters’
    ELSE
    PRINT ‘Does not contain “special” characters’

    Here its not filter ‘ø’ and ‘ü’ Please advice

  117. I WANT TO DISPLAY LAST_NAME OF ALL EMPLOYEES
    IN SUCH ORDER THAT FIRST NAME SHOULD START WITH ALPHABET A
    THEN WITH I COULD DISPLAY WITH ALPHATBET ‘T’ THEN ‘K’
    AND THEN ALL RECORDS COME WITH ALPHABETICAL ORDER LIKE

    ADITYA
    TOM
    KABANA
    A.
    B
    C
    D
    E
    F
    G
    H
    I
    G
    .
    .
    .

  118. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

  119. Its the fifth one sir that is :-

    SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

    In hope of your pleasent reply.

    Tushan Jain from indore india

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s