Last date to submit: SomeDate ???

I see many ways to solve this and the sample data doesn't really show all the possible situations that could arise.  Do you test the solutions on a larger sample data set?  

The sample data is generated as a table variable. The solution guidelines say to not use any table variables. Should the solution file,

a) Include the code for generating the data in a table variable,

b) Assume the data already exists in a table variable as in the provided code,

c) Include code to create the table in a standard table,

d) Presume the data exists in a standard table?

Not meaning to be OCD, I'm just wanting to provide the solution in the easiest and most appropriate format for you guys.

@EOstrander,

Think of the table variable as a real table that already contains all the necessary data. The solution file should include the sample table and data scripts.

@GoBlando,

Think of this problem in most simplistic terms.  The solution should replace the keyword, with a HTML hyperlink.  There really should be no outstainding issues here.  If an exact keyword exists in the text, replace the keyword; otherwise do nothing.

What will define an "exact match"? (already asked in the forum but with no reply yet...):

How to deal with upper/lower case mismatch?

What will be the allowed leading and/or following character to still make it an exact match?

Example: Jacob mentioned in the forum that keyword='SQL' and data='SQL,PHP' should lead to a hyperlink for SQL but not for 'SQLPHP' (he used a different example for the latter, though...).

I do se outstanding issues based on the current explanation available...

<<What will define an "exact match"? (already asked in the forum but with no reply yet...):

An exact match can be defined as a case insensitive match, but all characters must be represented... no more... no less.

<<What will be the allowed leading and/or following character to still make it an exact match?

The challenge does not have a specification for leading characters, as the focus of the challenge is more at the conceptional level. When you consider the extensive list of available characters that should/could be incorporated as leading characters it complicates the challenge, but for the wrong reasons. As you can see, I did not put any commas, weird characters, or varying spaces, within the sample data because it only makes the code more complicated and I wanted to focus on how to approach and solve the problem, not account for every possible scenario. With all that said, the only leading character should be a space, as presented in the sample data.

Heh... nice challenge, Adam.  The data setup code and the expected results explain it quite clearly.  I think this is going to be both fun and easy.

--Jeff Moden

HI Adam few questions ..

are these certain key words fixed as Microsoft,NewYork,Restaurant,TSQLChallenges ...

can we use udf's... solving the problem

I think this is too easy and open-ended.  I think the following Line should be added to the sample data:

UNION ALL

SELECT 7, 'TSQL', 'Welcome to the Microsoft Pet Store.'

UNION ALL

SELECT 8, 'Server', 'Not all Servers are like a SQL Server Database'

>>>>UDF's are not allowed :-)

Ummmm... not that I need one, but why not?  Heh... please don't say it's because they not set-based because I can show you some that are.  Besides, (admittedly, it's a long shot) you just might miss out on a stroke of genius by not allowing Cursors, While Loops, or UDF's.  You should accept any and all entries... not just the Set Based ones.

As you read the above, keep in mind that I'll usually be the first to summarily dismiss the notion of using any form of RBAR.  BUT, I do keep a watchful open because I've seen some very, very clever RBAR.

If you really want to make these challenges interesting, accept only one entry per person.  It will instill the same attitude I try to instill at work.. stop throwing stuff at the wall to see what will stick... do it right the first time. ;-)

--Jeff Moden

p.s. I just submitted my entry for Challenge 17.  

Heh... up to you on whether you want to accept more than one submission from folks.  I was just looking at how far behind the evals have gotten and thought it might be easier for all of you.

I have to ask based on your response... what do you folks consider a recursive CTE to be?  Set based or RBAR?  

Also, you haven't answered my other question...  why do you not allow UDF's?

--Jeff Moden

I agree that only one solution per person should be allowed.

This is an interesting discussion regarding recursive CTEs.  They are kind of a hybrid since they are implemented by the query engine as repeated nested table scans (RBAR) but are written using set-based language and thus inspire set-based thinking.

But ultimately, even set-based queries are implemented in a sense as RBAR, the query engine just uses a bunch of tricks (indexes, ordering, concatenation algorithms, spools, etc) to minimize which rows to look at and how often.  Take out your indexes and perform a join, and what do you have?  Nested table scans.  You could probably even write a cursor-based equivalent of the best set-based execution plan which would run at the set-based plan's speed times some constant, regardless of the input size.  After all, the query engine is implemented using C or C++, probably, and just uses loops when it executes the queries.

So, I am of the mind that recursive CTE should be considered set-based, albeit slow.  I am hopeful that MS will provide an "INDEXED ON" keyword (or something) that can be used to index the intermediate CTE tables.  That would really rock.

However, maybe this means cursor solutions should be allowed too.  But this might invite too many procedural programmers (GASP!) to submit solutions which are not in the spirit of SQL.

Anyway, keep the challenges coming, they are really fun.  Thank you!

I agree regarding one solution per person. When implemented, it should be allowed to change the posted solution until the challenge is closed. This would allow for making improvements. Otherwise there might be some kind of holding back until the very last possible moment. I dunno whether this would make any difference...  

I have a solution that uses a set based update, but you will have to iterate through the keywords.  It would be possible to do it completely set based if there were only one (or fewer) intersections, but there are two on row 2.  Also, the varchar must be expanded to 100 to account for the new text.  I iterate w/o a cursor as they are evil.  Since we're using a var table, the iteration is in memory and there is no cost to the scans.  If my iterative solution is considered RBAR, then I look forward to the set based solution.

Well, even the solution is using RBAR in this challenge, I don't think it will suffer from poor performance. Why? Well, look at the datatype of id column. It's tinyint.

; with ctea as (

 select id,

        keyword,

        data,

        levels = cast(0 as int)

 from @t

 union all

 select a.id,

        a.keyword,

        data = cast(replace(a.data, b.keyword, '<a href="tsql.com?id=' + cast(b.id as varchar) + '">' + b.keyword + '</a>') as varchar(300)),

        levels = a.levels+1

 from ctea a join @t b on charindex(b.keyword, a.data) > 0

 where not charindex(b.keyword+'</a>', a.data) > 0

),

cteb as (

select distinct id,

               keyword,

               data,

               DENSE_RANK() OVER (partition by id order by levels desc) as indexkey

from ctea

)

select id,

      keyword,

      data

from cteb

where indexkey = 1

order by id

@ Billy:

That looks like it works.  But try adding this line to your table:

UNION ALL SELECT 7, 'TSQL', 'Welcome to the Microsoft TSQL Pet Store.'

Your output ain't pretty (like my grammar).

Good point, thanks

DECLARE @t TABLE(

id      TINYINT,

keyword VARCHAR(20),

data    VARCHAR(300)

)

INSERT INTO @t(id, keyword, data)

SELECT 1, 'Pet Store', 'Microsoft SQL Server'

UNION ALL

SELECT 2, 'SQL Server Database', 'Dinner at a New York Restaurant'

UNION ALL

SELECT 3, 'Restaurant', 'Welcome to TSQL Challenges 17'

UNION ALL

SELECT 4, 'New York', 'Bob is a Database Expert'

UNION ALL

SELECT 5, 'TSQL Challenges', 'Is Microsoft Listening?'

UNION ALL

SELECT 6, 'Microsoft', 'New Challenges are coming up'

union all

SELECT 7, 'TSQL', 'Welcome to the Microsoft TSQL Pet Store.'

UNION ALL

SELECT 8, 'Server', 'Not all Servers are like a SQL Server Database'

; with ctea as (

 select id,

        keyword,

        data,

        levels = cast(0 as int)

 from @t

 union all

 select a.id,

        a.keyword,

        data = cast(replace(a.data, b.keyword, '<a href="tsql.com?id=' + cast(b.id as varchar) + '">' + b.keyword + '</a>') as varchar(300)),

        levels = a.levels+1

 from ctea a join @t b on charindex(b.keyword, a.data) > 0

 where not charindex(b.keyword+'</a>', a.data) > 0

   and not patindex('%<%a href="' + b.keyword + '%>%', a.data) > 0

   and a.id <> b.id

),

cteb as (

select distinct

      id,

      keyword,

      data,

      DENSE_RANK() OVER (partition by id order by levels desc) as indexkey

from ctea

)

select id,

      keyword,

      data     = MAX(data)

from cteb

where indexkey = 1

group by id,

        keyword

order by id

Billy,

Why doesn't your solution replace phrases which are in the same row as the keyword?

SELECT 8, 'Server', 'Not every Server is like a SQL Server Database'

"Server" does not get replaced.

@ksuchlicki:

That is because the word in that line is "Servers" not server.  It properly replaced "Sql Server" first.

@Billy:

Not to be that guy, but check out your result on Line 7... TSQL is missing a tag.  Also, the specs are sort of grey, but it was implied that you should take the earliest ID that contains the keyword, so in line 3 TSQL Challenges should be tagged sinces its ID is 5

@billy:

Check the forum, I posted my sample data I used to test with the results of my query.  I am pretty sure I have it accurately, but others and yourself can test.

GoBlando,

Right, but I modified the line:

SELECT 8, 'Server', 'Not every Server is like a SQL Server Database'

It still does not get replaced.

I have a very late submission for this challenge. I understand that it could not be part of the winners due to late submission, but I would like it evaluated. Can i upload my solution

Jacob,

While performance testing, is it possible you change the basic data types of input tables. Like in this example data type of Id field is TINYINT and so for performance testing might be for million records you need to change data type from TINYINT to INT.

If so, in that case if we had used the same datatype (ie. TINYINT ) in the solution, that solution will fail during performance testing, or in automated testing process the care for such situation is been taken of?

-Pragnesh