SQLTeam.com | Weblogs | Forums

SQL pattern search

Hi ,, I have this SP that has a pattern search, not too familiar with it can some one walk me through it?

IF PATINDEX('%Director: %', @actTextDisplay) > 0
SET @actTextDisplay_DiectorIndex = PATINDEX('%Director: %', @actTextDisplay)
IF (CHARINDEX('.', @actTextDisplay, @actTextDisplay_DirectorIndex) - @actTextDisplay_DirectorIndex) > 0
SET @actTextDisplay_DirectorLength = CHARINDEX('.', @actTextDisplay, @actTextDisplay_DirectorIndex) - @actTextDisplay_Directorindex
SET @actTextDisplay_DirectorLength = LEN(@actTextDisplay) + 1 - @actTextDisplay_DirectorIndex

  if len(ISNULL(@referToDirectID,'')) = 36 
  	SELECT @txt_Refer_to_recipient_id = CONVERT(VARCHAR(36),ORR.uniq_id)
  	FROM Table A

first things first

what is the difference between


that adds to the confusion


hope this helps ..

Thanks NO difference.. may be a typo.

Thanks Harish...

So where the length 19 came from , "who am i to judge director? got it to a bit, just the 19?

The code looks for the string 'Director :' in @actTextDisplay. It then looks to see if a period ('.') appears after that string. If so, then it extracts the part of the string after 'Director: ' and before the period. If there is not a period, then it uses the entire rest of the string after 'Director:'.

It can be simplified to this with the same result:

SET @actTextDisplay_DirectorIndex = CHARINDEX('Director: ', @actTextDisplay)
IF @actTextDisplay_DirectorIndex > 0
    SET @actTextDisplay_DirectorLength = CHARINDEX('.', @actTextDisplay + '.',
        @actTextDisplay_DirectorIndex) - @actTextDisplay_Directorindex

Thanks Scott! Now I understand it! Always the best answer.. You are the master!
Thanks Again!

Sorry forgot to ask what is the Minus sign between --> @actTextDisplay_DirectorIndex) - @actTextDisplay_Directorindex...


It's subtraction.

The CHARINDEX('.', ...) finds the byte location of the period after the 'Director: ' string (note that I add a '.' to the string so that a '.' will always be found, eliminating the need for the IF/ELSE in the original code) . Variable @actTextDisplay_DirectorIndex already contains the byte where 'Director: ' starts. To get the length of the string from 'Director: ' to the dot/end of string, we simply subtract the two. For example:

'any old text Director: director_id_goes_here. and some more text'
Starts in byte 14 (Director:), ends in byte 45(.), 45 - 14 = len of 31

Btw, the code:
if len(ISNULL(@referToDirectID,'')) = 36
SELECT @txt_Refer_to_recipient_id = CONVERT(VARCHAR(36),ORR.uniq_id)
FROM Table A
is still needed, of course, I just adjusted the code before that, that calc'd the length.


Awesome thanks!

The best way to say thanks is to like the reply
and flag the best answer as solution.

1 Like

Thanks for transferring knowledge and experience.