SQL pattern search

Transact-SQL
1

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

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

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

first things first

what is the difference between

@actTextDisplay_DiectorIndex;
@actTextDisplay_DirectorIndex;

that adds to the confusion

3

hi

hope this helps ..

image
image972×439 15.5 KB

4

Thanks NO difference.. may be a typo.

5

Thanks Harish...

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

6

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
2 Likes
7

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

8

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

Pasi.

9

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:

--23456789!123456789@123456789#123456789$123456789%123456789^
'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
BEGIN
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.

2 Likes
10

Awesome thanks!

11

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

1 Like
12

Thanks for transferring knowledge and experience.