Good SQL Fizz Buzz

Slight Rewrite (developed it over a coffee break):

--Given

Declare @OldNode Varchar(5)
Select @OldNode = '90909'

Declare @OldPath  varchar(1000)
Select @OldPath = '//90012/90909/90000'

--and
--if(Len(@OldNode) = 5) -- This will always return true

/*

Using TSQL:

1)Assign the value of '90606' to a new variable called NewNode.
2)Using OldNode and OldPath replace the instance of OldNode in OldPath with the value of NewNode and assign the result to a new variable called NewPath. Print the results to Messages. 

*/

Thats the bonus question I did a slight rewrite to clarify the question.

And if you get asked what '--' you can save yourself all kinds of time interviewing someone.

Well, if the candidate understood TSQL comments, would the next part be identify the mission closing quotation?

I remember reading Atwoods article and thinking to myself (after knowing how I would do it in C#) how would I ask that question and answer it in TSQL?

The answers others posted online after that were very interesting! I had to admit if I had been interviewed on the spot with that question I could not have been as creative in TSQL as many others were.

Sadly, I had to look up the syntax for replace and I'm assuming that you mean using the print function when you refer to messages?  Or are you talking about raiseerror.

odujosh wrote:

Declare @OldNode = '90909'

Is this even valid TSQL syntax?

JChung2006 wrote:



odujosh wrote: Declare @OldNode = '90909'

Is this even valid TSQL syntax?

Lazyness fixed sorry. Wish TSQL was smarter

ScanIAm wrote:

Sadly, I had to look up the syntax for replace and I'm assuming that you mean using the print function when you refer to messages?  Or are you talking about raiseerror.

Hint: Requires Left, Right, LEN, and PatIndex You could also write it with Substring if you enjoy pain or are more comfi.

I would accept Print or Select. Really knowing the differences is trivial. Seeing whether you can think through the problem is more interesting.

'You' being sample candidate.

Just write a SQL-CLR function and call that

PerfectPhase wrote:

Just write a SQL-CLR function and call that

Hahahaha. Certainly that would be the best choice in the present day.

People forget about fundamentals and what it was like walking uphill both ways to school. They other day I was talking with a friend about a UDF I had dropped into a database for an old client that performed a RegEx search pretty similar to Ken Henderson's sample here and he was baffled by my approach.

I forgot to add it was SQL Server 2000 base being accessed by a classic ASP request. I thought I read an article this week or last (I want to say it was by Rob Conery) about how we are now at a point where a lot of people only know .NET already. The concept and clunkiness of old school approaches are now foreign. I think that is good and bad, personally.

PerfectPhase wrote:

Just write a SQL-CLR function and call that

Start a why use something you don't need argument here.

PerfectPhase wrote:

Just write a SQL-CLR function and call that

Congratulations!  You've turned a simple code problem into a deployment headache?

Gee, a nice sample. But the list of tasks should actually be
 
* find a flaw in the materialized path persistence design (based on the four lines of code above)
* propose a fix
* write a piece of code to replace node A with node B and reparent node A's children
* and yeah, print whatever the result is

odujosh wrote:



ScanIAm wrote: Sadly, I had to look up the syntax for replace and I'm assuming that you mean using the print function when you refer to messages?  Or are you talking about raiseerror.

Hint: Requires Left, Right, LEN, and PatIndex You could also write it with Substring if you enjoy pain or are more comfi.

I would accept Print or Select. Really knowing the differences is trivial. Seeing whether you can think through the problem is more interesting.

'You' being sample candidate.

I'm not sure if you changed the criteria, but once you declare the missing variables and load them up, the replacement requires 1 line of code:

Select @NewPath = REPLACE(@OldPath, @OldNode, @NewNode)

Am I missing something?

ScanIAm wrote:



odujosh wrote:  ScanIAm wrote: Sadly, I had to look up the syntax for replace and I'm assuming that you mean using the print function when you refer to messages?  Or are you talking about raiseerror. Hint: Requires Left, Right, LEN, and PatIndex You could also write it with Substring if you enjoy pain or are more comfi. I would accept Print or Select. Really knowing the differences is trivial. Seeing whether you can think through the problem is more interesting. 'You' being sample candidate.

I'm not sure if you changed the criteria, but once you declare the missing variables and load them up, the replacement requires 1 line of code:

Select @NewPath = REPLACE(@OldPath, @OldNode, @NewNode)

Am I missing something?

Definitely you are, try the case with OldPath set to "//4789237/90909/9090901/909092"
and see what happens.
But the point is that the original design is suboptimal (which can be considered flawed).

ScanIAm wrote:



odujosh wrote:  ScanIAm wrote: Sadly, I had to look up the syntax for replace and I'm assuming that you mean using the print function when you refer to messages?  Or are you talking about raiseerror. Hint: Requires Left, Right, LEN, and PatIndex You could also write it with Substring if you enjoy pain or are more comfi. I would accept Print or Select. Really knowing the differences is trivial. Seeing whether you can think through the problem is more interesting. 'You' being sample candidate.

I'm not sure if you changed the criteria, but once you declare the missing variables and load them up, the replacement requires 1 line of code:

Select @NewPath = REPLACE(@OldPath, @OldNode, @NewNode)

Am I missing something?

REPLACE is not necessary. OldNode only occurs once. And ScanIAm: Length will always be 5. This limits the design to only in number of groups. The number of groups will always be under 10,000 closer to in the hundreds. Its not flawed the designer realizes the limitation.

Though you do show why fixed length is a benifit.

JChung2006 wrote:



PerfectPhase wrote: Just write a SQL-CLR function and call that

Congratulations!  You've turned a simple code problem into a deployment headache?

What?  It's just another bit of SQL to deploy it and if your using the CLR already, why not.