Find and replace third occurrence of the string

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 6, 2018 at 11:29 pm

  #364564

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 7, 2018 at 11:31 am

  #2008467

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  I'll make you a deal.  Tell me why you need to do this and I'll tell you how to do it. 😀  I'd tell you first and then ask why but most people end up saying "Because it's a requirement I was given", which tells me nothing, or they never respond.  What is the business reason behind doing this?

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

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 7, 2018 at 11:55 am

  #2008470

  Jeff Moden - Sunday, October 7, 2018 11:31 AM

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  I'll make you a deal.  Tell me why you need to do this and I'll tell you how to do it. 😀  I'd tell you first and then ask why but most people end up saying "Because it's a requirement I was given", which tells me nothing, or they never respond.  What is the business reason behind doing this?

  I went to Bank of America interview yesterday This question is asked in yesterday's interview. When I searched for answer in internet for this question  there is direct answer available in oracle (Regex_Replace) but no direct  solution in SQL server. So asked in this forum for help.

  Saravanan

• Jonathan AC Roberts

  SSCoach

  Points: 18361

  More actions

  October 7, 2018 at 1:56 pm

  #2008477

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  You can do this with CROSS APPLY:
  select stuff (productdescription,C.Third,1,'A') WithReplacement, *
from store s
cross apply(values (charindex('o',s.productdescription))) A([First])
cross apply(values (charindex('o',s.productdescription,A.[First]+1))) B([Second])
cross apply(values (charindex('o',s.productdescription,B.[Second]+1))) C(Third)


• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 7, 2018 at 2:09 pm

  #2008478

  saravanatn - Sunday, October 7, 2018 11:55 AM

  Jeff Moden - Sunday, October 7, 2018 11:31 AM

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  I'll make you a deal.  Tell me why you need to do this and I'll tell you how to do it. 😀  I'd tell you first and then ask why but most people end up saying "Because it's a requirement I was given", which tells me nothing, or they never respond.  What is the business reason behind doing this?

  I went to Bank of America interview yesterday This question is asked in yesterday's interview. When I searched for answer in internet for this question  there is direct answer available in oracle (Regex_Replace) but no direct  solution in SQL server. So asked in this forum for help.

  200 points for the honest answer, Saravanan!

  Here's the exact answer that I would have given, word for word.  You might want to write this down in your "How to win any interview you want" book because the rest of the books out there on the subject make people look like idiots.
  

  
  In SQL Server, you could call RegEx Replace by using a CLR.  I don't actually know how to do that but that's because in head-to-head races several years ago, a couple of us on SQL Server Central discovered that if you can get SQL Server to do something, you can usually get it to do it faster than a call to RegEx Replace.

  Also, code designed to do just one thing is almost always faster than something that's flexible.  With that in mind, if you know that you'll always want to replace the third instance of a given character, then 3 nested CHARINDEX functions would work the fastest to isolate the instance and then use STUFF to replace it and it will beat a CLR call to RegEx Replace.  And, yes, the character or characters to search for could be variable even in this type of code.  If you wanted it to be case sensitive, then you could add a COLLATION clause to force a binary collation, which would also make it faster still, in most cases.

  For more flexible code, you could write dynamic SQL to build enough nested CHARINDEX functions but that can become quite unwieldy and slow if you need to go much further than the 3rd instance, so I wouldn't do that.  Some might also use an rCTE (Recursive CTE) to find that third instance, keeping track of the position as the rCTE unfolded but rCTEs that increment a count are as slow as a poorly written WHILE loop and can be beaten by a well written WHILE loop and they use about 8 times more logical I/O (reads) than a WHILE loop, so I wouldn't use an rCTE either.

  So that would bring me around to the "Swiss Army Knife" of T-SQL, the Tally Table and its readless equivalent, the Cascading CTE (cCTE), first introduced by Itzek Ben-Gan.  One fast way that would be flexible enough to use for any number of instances would be to use the cCTE to find all the positions of the desired character to isolate the instances and use ROW_NUMBER() OVER to identify the desired instance number to replace and then STUFF the replacement character into place.  Since this could all be done in a single "query" consisting of cascading queries, it could easily be encapsulated for reuse in a very high performance "Inline Scalar Function" (or iSF), which is really an "Inline Table Valued Function" (or iTVF) that returns just one scalar value but still blows the doors off of a call to RegEx Replace and scalar functions both.

  Sounds like the long way to go about things but that bit of rhetoric shows them just exactly how much you know about what will work but not well and why and what will work quickly.  You've probably introduced them to some terms they're not already aware of and taught them what they mean in the process.  If they already know terms like what an iTVF is, they'll be very happy that you also know.  If they're thinking the correct answer is an rCTE, you've also shown them that it is not without calling them dummies.  😀

  Now, you have to be ready for the next question which will be (especially if they were expecting the rCTE answer) "Can you show us on the white board?" or "Can you write the code on this laptop?"  You should only have one answer for that.  "Sure... it'll be my pleasure".  And then actually be able to do it.  If you're good, explain to them what each piece does as you write it.  That will emphasize not only your ability to communicate, but your ability and willingness to teach and share knowledge.

  With the idea of teaching a man to fish, take a crack at it using the description I posted above and post it back here.  The denizen's on this forum love these kinds of problems and you'll learn more than you can shake a fish at in the process.  Seriously.  Give it a shot.  We'll help once you've given it a good, honest try.

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

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  October 7, 2018 at 2:14 pm

  #2008479

  Jonathan AC Roberts - Sunday, October 7, 2018 1:56 PM

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  You can do this with CROSS APPLY:
  select stuff (productdescription,C.Third,1,'A') WithReplacement, *
from store s
cross apply(values (charindex('o',s.productdescription))) A([First])
cross apply(values (charindex('o',s.productdescription,A.[First]+1))) B([Second])
cross apply(values (charindex('o',s.productdescription,B.[Second]+1))) C(Third)


  That works until you come across a string that has less than 3 o's in the string...
  As you have it written, a string with 0 or 2 o's will return NULL and if there is a only 1 o, it will just get replaced.

  That said, the problem is easy enough to fix by NULLing out CHARINDEX() = 0...

  IF OBJECT_ID('tempdb..#store', 'U') IS NOT NULL
BEGIN DROP TABLE #store; END;
GO

  CREATE TABLE #store (
    productid INT,
    productdescription VARCHAR (200)
);
INSERT INTO #store VALUES
    (1, 'Tata owned original walnut is rich very in omega-3 '),
    (2, 'olive oil is very healthy compared to other oil'),
    (3, 'This is a string.'),
    (4, 'This is an o-string.'),
    (5, 'This is an oo-string.'),
    (6, 'This is an ooo-string.'),
    (7, 'This is an oooo-string.');

      
--=================================================================================

  SELECT
    s.*,
    answer = ISNULL(STUFF(s.productdescription, cl3.char_loc_3, 1, 'A'), s.productdescription)
FROM
    #store s
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, 1), 0)) ) cl1 (char_loc_1)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, cl1.char_loc_1 + 1), 0)) ) cl2 (char_loc_2)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, cl2.char_loc_2 + 1), 0)) ) cl3 (char_loc_3);

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 17, 2018 at 5:41 am

  #2010017

  Jason A. Long - Sunday, October 7, 2018 2:14 PM

  Jonathan AC Roberts - Sunday, October 7, 2018 1:56 PM

  saravanatn - Saturday, October 6, 2018 11:29 PM

   I want to find the  third occurrence of the 'o' and replace with 'A'

  
create table store
(
productid int,
productdescription varchar(200)
);
insert into store values (1,'Tata owned original walnut is rich very in omega-3 ');
insert into store values (2,'olive oil is very healthy compared to other oil');


  I am able to find the second occurrence of the string. using the below query

  
select stuff (productdescription,
charindex('o',productdescription,charindex('o',productdescription)+1),1,'A')
from store;


  You can do this with CROSS APPLY:
  select stuff (productdescription,C.Third,1,'A') WithReplacement, *
from store s
cross apply(values (charindex('o',s.productdescription))) A([First])
cross apply(values (charindex('o',s.productdescription,A.[First]+1))) B([Second])
cross apply(values (charindex('o',s.productdescription,B.[Second]+1))) C(Third)


  That works until you come across a string that has less than 3 o's in the string...
  As you have it written, a string with 0 or 2 o's will return NULL and if there is a only 1 o, it will jet replaced.

  That said, the problem is easy enough to fix by NULLing out CHARINDEX() = 0...

  IF OBJECT_ID('tempdb..#store', 'U') IS NOT NULL
BEGIN DROP TABLE #store; END;
GO

  CREATE TABLE #store (
    productid INT,
    productdescription VARCHAR (200)
);
INSERT INTO #store VALUES
    (1, 'Tata owned original walnut is rich very in omega-3 '),
    (2, 'olive oil is very healthy compared to other oil'),
    (3, 'This is a string.'),
    (4, 'This is an o-string.'),
    (5, 'This is an oo-string.'),
    (6, 'This is an ooo-string.'),
    (7, 'This is an oooo-string.');

      
--=================================================================================

  SELECT
    s.*,
    answer = ISNULL(STUFF(s.productdescription, cl3.char_loc_3, 1, 'A'), s.productdescription)
FROM
    #store s
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, 1), 0)) ) cl1 (char_loc_1)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, cl1.char_loc_1 + 1), 0)) ) cl2 (char_loc_2)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('o', s.productdescription, cl2.char_loc_2 + 1), 0)) ) cl3 (char_loc_3);

  Thanks all for  providing your valuable insight and solutions

  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 17, 2018 at 6:08 am

  #2010024

  Just an interview tip...  if they ask you something like this and you don't ask if it will always be just the 3rd occurrence or if it could be ANY occurrence in the future, you may lose the interview to someone who does.

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

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 17, 2018 at 6:18 pm

  #2010164

  Jeff Moden - Wednesday, October 17, 2018 6:07 AM

  Just an interview tip...  if they ask you something like this and you don't ask if it will always be just the 3rd occurrence or if it could be ANY occurrence in the future, you may lose the interview to someone who does.

  I didn't ask the question and didn't clear the interview as well. But next time  definitely will ask the question.
  Thanks Jeff

  Saravanan

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  October 18, 2018 at 2:25 pm

  #2010310

  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.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 18, 2018 at 4:01 pm

  #2010321

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

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

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  October 18, 2018 at 4:19 pm

  #2010323

  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.

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

  Fascinating.  That's not my approach, either when interviewing or being interviewed.

  When interviewing, I still think interviewees should answer the q they were asked, providing all critical info but not beyond that.  Unless perhaps it's a severe enough situation to cause one to not want the job at all.  You can always follow up with your preferred details in your own portion of the interview, where they ask you for qs/comments.  At Smith&Nephew I sometimes had to interview a total of 20 or more people to find someone (even decent DBAs are hard to find, let alone good ones).  I didn't have time for everyone to expound on their personal views on all related methods.  For example, if I ask about FKs, I don't want a lecture on why I shouldn't use triggers to enforce them, but should use actual FKs instead, because I already know that.  Likewise probably with whatever I'm asking you about.  Assuming I'm ignorant enough about the topic I'm asking about to need a mini-lesson during an interview is not what I consider a good interviewing approach.  But perhaps I'm overly harsh on that point.

  Likewise, when being interviewed, I try to answer their actual qs as directly as reasonably possible, but without leaving out critical additional facts, if needed.  Typically only if they don't offer me an opportunity later to add comments would I consider it serious enough to not want to work there at all.  I don't do an interview unless I'm genuinely interested in that job.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 18, 2018 at 8:33 pm

  #2010347

  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?

  Saravanan

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 18, 2018 at 8:42 pm

  #2010349

  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)
  

  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 18, 2018 at 8:44 pm

  #2010351

  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.

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