Find and replace third occurrence of the string

  • Jeff Moden - Thursday, October 18, 2018 8:44 PM

    saravanatn - Thursday, October 18, 2018 8:33 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    I have to give best possible solution in the first and then explain him why I chose that particular answer instead of other possible solutions . Is my understanding correct?

    Like anything else, I'll say "It Depends".  It mostly depends on how you "read the room"  Like I said, I've been on interview teams that have people that stop listening if they don't hear what they think the solution should be even though, if they listened, they might be hearing a better answer.

    Exactly

    Saravanan

  • Just for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length. 

    DECLARE
    @find  VARCHAR(100) = 'o',
    @replace VARCHAR(100) = 'A',
    @instance INT   = 3;

    SELECT
    s.productid,
    s.productdescription,
    answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
    FROM #store AS s
    LEFT JOIN
    (
    SELECT s.productid, s.productdescription, ng.position,
       rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
    FROM #store AS s
    CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
    WHERE ng.token = @find
    ) AS f ON s.productid = f.productid AND f.rn = @instance;

    If I change the parameters like so (for example):
    DECLARE
    @find  VARCHAR(100) = 'is',
    @replace VARCHAR(100) = 'XX',
    @instance INT   = 2;

    I can replace the second instance of "is" with "XX"

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B - Monday, October 22, 2018 3:19 PM

    Just for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length. 

    DECLARE
    @find  VARCHAR(100) = 'o',
    @replace VARCHAR(100) = 'A',
    @instance INT   = 3;

    SELECT
    s.productid,
    s.productdescription,
    answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
    FROM #store AS s
    LEFT JOIN
    (
    SELECT s.productid, s.productdescription, ng.position,
       rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
    FROM #store AS s
    CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
    WHERE ng.token = @find
    ) AS f ON s.productid = f.productid AND f.rn = @instance;

    If I change the parameters like so (for example):
    DECLARE
    @find  VARCHAR(100) = 'is',
    @replace VARCHAR(100) = 'XX',
    @instance INT   = 2;

    I can replace the second instance of "is" with "XX"

    Sorry Alan I should have read Ngrams8K before 4 month . It is well thought and well written article. You put lot of efforts in writing that article. Thanks!!!!

    Saravanan

  • saravanatn - Monday, February 11, 2019 10:09 AM

    Alan.B - Monday, October 22, 2018 3:19 PM

    Just for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length. 

    DECLARE
    @find  VARCHAR(100) = 'o',
    @replace VARCHAR(100) = 'A',
    @instance INT   = 3;

    SELECT
    s.productid,
    s.productdescription,
    answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
    FROM #store AS s
    LEFT JOIN
    (
    SELECT s.productid, s.productdescription, ng.position,
       rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
    FROM #store AS s
    CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
    WHERE ng.token = @find
    ) AS f ON s.productid = f.productid AND f.rn = @instance;

    If I change the parameters like so (for example):
    DECLARE
    @find  VARCHAR(100) = 'is',
    @replace VARCHAR(100) = 'XX',
    @instance INT   = 2;

    I can replace the second instance of "is" with "XX"

    Sorry Alan I should have read Ngrams8K before 4 month . It is well thought and well written article. You put lot of efforts in writing that article. Thanks!!!!

    Thanks for the kind words!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • saravanatn - Thursday, October 18, 2018 8:42 PM

    Jeff Moden - Thursday, October 18, 2018 4:01 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    It does depend on the environment that you're interviewing in.  I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer.  In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.

    The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉

    Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.

    SELECT MAX(salary) FROM employee
    WHERE salary NOT IN (  SELECT MAX (salary) FROM employee)

    If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John - Monday, February 11, 2019 12:53 PM

    saravanatn - Thursday, October 18, 2018 8:42 PM

    Jeff Moden - Thursday, October 18, 2018 4:01 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    It does depend on the environment that you're interviewing in.  I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer.  In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.

    The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉

    Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.

    SELECT MAX(salary) FROM employee
    WHERE salary NOT IN (  SELECT MAX (salary) FROM employee)

    If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!

    Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:


    SELECT TOP (1) salary FROM ( SELECT TOP (2) salary FROM employee ORDER BY salary DESC ) AS qry
    ORDER BY salary

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I was just asked for a job. And was asked if I could help with an overview of some info. Told how I would solve the problem with a simple solution. I was thanked and didn't get the job. 

    Ben
    I was just asked for a job by a collegue, with my anwser he could solve the problem himself in a simple way, so he did not need me for this job. 

    This time:
    Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime.
    Next time:
    Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
    (Free after Terry Pratchett)

  • ScottPletcher - Monday, February 11, 2019 1:03 PM

    Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:

    What would be the function of an index on salary ?

    (I can immagine overviews or that during processing the height of the salary has in impact (for example in the where clause), but can not imagine an index which would be really usefull. Even for a large company say a 100 000 employee's, I can not think of a single really usefull function. )

    Ben

  • ScottPletcher - Monday, February 11, 2019 1:03 PM

    Michael L John - Monday, February 11, 2019 12:53 PM

    saravanatn - Thursday, October 18, 2018 8:42 PM

    Jeff Moden - Thursday, October 18, 2018 4:01 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    It does depend on the environment that you're interviewing in.  I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer.  In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.

    The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉

    Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.

    SELECT MAX(salary) FROM employee
    WHERE salary NOT IN (  SELECT MAX (salary) FROM employee)

    If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!

    Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:


    SELECT TOP (1) salary FROM ( SELECT TOP (2) salary FROM employee ORDER BY salary DESC ) AS qry
    ORDER BY salary

    Thanks Scott .

    Saravanan

  • Michael L John - Monday, February 11, 2019 12:53 PM

    saravanatn - Thursday, October 18, 2018 8:42 PM

    Jeff Moden - Thursday, October 18, 2018 4:01 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    It does depend on the environment that you're interviewing in.  I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer.  In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.

    The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉

    Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.

    SELECT MAX(salary) FROM employee
    WHERE salary NOT IN (  SELECT MAX (salary) FROM employee)

    If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!

    But it depends upon the person their knowledge and their attitude. Some people are happy with what they got and some others are ambitious . You can't blame both .

    Saravanan

  • Michael L John - Monday, February 11, 2019 12:53 PM

    saravanatn - Thursday, October 18, 2018 8:42 PM

    Jeff Moden - Thursday, October 18, 2018 4:01 PM

    ScottPletcher - Thursday, October 18, 2018 2:25 PM

    I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in that.  I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.

    It does depend on the environment that you're interviewing in.  I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer.  In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.

    The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉

    Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.

    SELECT MAX(salary) FROM employee
    WHERE salary NOT IN (  SELECT MAX (salary) FROM employee)

    If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!

    Why?  They're just asking rudimentary questions to test the interviewee's knowledge of T-SQL in a non-esoteric fashion.  It's why I start with the "How do you get the current date and time using T-SQL" question in every interview.  I typically follow that with a series of very simple questions that any "fresher" should be able to answer (only to find out people that claim 10 years of experience don't know).  I'll also tell you that these introductory/get comfortable questions are the same whether I'm interviewing a DBA, Database Developer, or Application/Web Developer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ben.brugman - Thursday, February 14, 2019 2:32 AM

    ScottPletcher - Monday, February 11, 2019 1:03 PM

    Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:

    What would be the function of an index on salary ?

    (I can immagine overviews or that during processing the height of the salary has in impact (for example in the where clause), but can not imagine an index which would be really usefull. Even for a large company say a 100 000 employee's, I can not think of a single really usefull function. )

    Ben

    If a place continually looked at top 3, say, salaries?  Or compared the top n salaries of one category of employee to another.  My first job was at a public university, and they had endless request for top salaries by race and gender by job code, etc..  It's like the entire Board of Regents spent all their time comparing salaries to, I guess, try to "balance" them out.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 12 posts - 16 through 26 (of 26 total)

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