SQL question ? How can we find all the occurrences of a text in a string

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 4, 2017 at 1:14 pm

  #1931676

  Jeff Atherton - Friday, March 3, 2017 12:00 PM

  J Livingston SQL - Friday, March 3, 2017 11:42 AM

  not sure about this result......??

  DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)

  SET @String ='The SQL SERVER is one of the best applications of the Microsoft World and therefore is any further proof required?'
SET @Search_String='the'

  declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''

  WHILE @i < LEN(@String)
BEGIN

    if SUBSTRING(@String,@i,@stringLen) = @Search_String
   begin
   set @Result = @Result + CAST(@i as varchar(100)) + ','
   end
  SET @i = @i + 1
END

  SET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)

  SELECT @Result

  Good catch. Thanks for the QA Testing.  Here's an Update:

  
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)

  SET @String ='The SQL SERVER is one of the best applications of the Microsoft World and therefore is any further proof required?'
SET @Search_String='the '

  declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''

  WHILE @i < LEN(@String)
BEGIN

  if (SUBSTRING(@String,@i,@stringLen) = @Search_String) AND (SUBSTRING(@String,@i + @stringLen,1) = CHAR(32))
  begin
  set @Result = @Result + CAST(@i as varchar(100)) + ','
  end
SET @i = @i + 1
END

  SET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)

  SELECT @Result


  Although this would probably still miss one if the search string was the last word in the string.

  If the intent of the code is to find whole words, then your code still has a problem more than just finding the last word.  Try it with the following string.
  
SET @String ='The view would take your breath away and the calm of it all would soothe your soul.'


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

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 4, 2017 at 3:23 pm

  #1931678

  Jeff Atherton - Friday, March 3, 2017 12:26 PM

  Luis Cazares - Friday, March 3, 2017 12:17 PM

  Remember Murphy's Law: Anything that can go wrong, will go wrong.

  At the moment it might not be a problem, but why would you want to wait until it is urgent to code it the best possible way? I've had to fix a lot of legacy code because people thought that it wasn't a problem at the moment.

  The way the question was stated the request was to pass a string and a search string and then get back a comma separated list of the starting positions for each occurrence of that search string. That sounds a lot like this is going to be one at a time. Now if they need to do a SELECT and have this result as a column for millions of records then that's another story. But that's not what was stated.

  The problem is that even you stated that the code could be put into a function for easy reuse.  Who wouldn't?  Once that's done, you have no control over who uses it for what.  If you write it with a WHILE loop, it's usually (there are exceptions) going to be slow because 1) WHILE loops are inherently slow and 2) if a function has a WHILE loop in it, it can only be used in a Scalar function or an mTVF (multi-statement Table Valued Function), both of which add more slowness (and there are no exceptions to that).

  If it's worth writing once, it's worth writing it right so that you don't have to write it again when the story changes... and the story always changes. 😉

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