More wildcard searches

Steve Jones - SSC Editor

SSC Guru

Points: 734444
More actions
October 27, 2015 at 9:03 pm

#73652

Comments posted to this topic are about the item More wildcard searches

Viewing 15 posts - 1 through 15 (of 85 total)

You must be logged in to reply to this topic. Login to reply

emptytrash Grasshopper Points: 14 More actions · Answer 1

CREATE TABLE MyTextTable

(

myid INT

, mytext varchar(MAX)

);

GO

INSERT dbo.MyTextTable

( myid, mytext )

VALUES

( 1, 'A good coffee bean is important for brewing a good cup of coffee.' )

, ( 2, 'Coffee fuels the development process.' )

, ( 3, 'Coffee is beloved by everyone.' )

, ( 4, 'Most coffee brewers do not get hot enough.' )

, ( 5, 'People in the US tend to prefer coffee over tea' )

;

GO

If I run this, how many rows are returned?

SELECT

myid

FROM

dbo.MyTextTable AS mtt

WHERE

mytext LIKE '%coffee %';

>Sorry - you were wrong

>Correct answer:

>4

>Explanation:

>All of the rows are returned, as all have "coffee" as a word with a space after the word in the text.

>Ref: LIKE - https://msdn.microsoft.com/en-us/library/ms179859.aspx

>Wildcard Searches - http://www.sqlservercentral.com/articles/T-SQL/130558/

Why is the answer 4!???

happycat59 One Orange Chip Points: 29387 More actions · Answer 2

How can the "correct Answer" be 4 when all rows are returned. By my reckoning there are 5 rows !

HappyGeek SSCoach Points: 18726 More actions · Answer 3

Sorry can't see it; all the rows are returned is the correct answer. 5 rows were inserted with the word "coffee " (with a space), therefore 5 rows should be returned.

The only exception to this would be if the database in question were Case Sensitive, in which case I would expect the answer to be 3! But since this was not specified I think the general assumption is that the database is Case Insensitive.

To get 4 there must be a missing delete command, or the inserted text was typed in incorrectly.

Hold on..............

Looking again at the inserted values, did you miss the fact that the first line has the word coffee in twice, one of which is followed by a full stop, while the other is followed by a space, in which case it would still be counted?

...