Tech Off Thread

17 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Good SQL Fizz Buzz

Back to Forum: Tech Off
  • User profile image
    odujosh

    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. 

    */

  • User profile image
    odujosh

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

  • User profile image
    odujosh

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

  • User profile image
    Johnny​Awesome

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

    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.

  • User profile image
    ScanIAm

    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.

  • User profile image
    JChung2006

    odujosh wrote:
    Declare @OldNode = '90909'

    Is this even valid TSQL syntax?

  • User profile image
    odujosh

    JChung2006 wrote:
    
    odujosh wrote:
    Declare @OldNode = '90909'

    Is this even valid TSQL syntax?


    Lazyness fixed sorry. Wish TSQL was smarter Smiley

  • User profile image
    odujosh

    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.

  • User profile image
    PerfectPhase

    Just write a SQL-CLR function and call that Smiley

  • User profile image
    Johnny​Awesome

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


    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.

  • User profile image
    odujosh

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


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

  • User profile image
    JChung2006

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

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

  • User profile image
    sokhaty

    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

  • User profile image
    ScanIAm

    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?

  • User profile image
    sokhaty

    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).

  • User profile image
    odujosh

    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.

  • User profile image
    PerfectPhase

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

    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. 

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.