Using PATINDEX to find and extract multiple occurrences of a alphanumeric String

• rangamails

  Old Hand

  Points: 331

  More actions

  May 15, 2012 at 8:21 pm

  #269046

  [font="Courier New"]Hi All,

  How can we use PATINDEX to find multiple occurrences of a string in a column?

  I'm struck up with extracting a particular string from a column in a real-time scenario.

  Here's a brief overview:

  I've a SQL table with 4 columns in it. One is SUBCAT, REF_FROM, REF_TO and CASE_HISTORY.

  There are different subcategories in the SUBCAT column - Of which i only need to look for a particular subcategory (called CORPORATE_NEW) for which i need to extract the emp. code based on specific conditions.

  For CORPORATE_NEW subcategory, i need to extract the 5 or 6 digit emp. code

  For other subcategories, i need to extract the 10 digit emp. code

  The columns REF_FROM and REF_TO will have 4 different types employee codes (alphanumeric strings of length 5, 6, 9 or 10) and will have the latest emp. codes for a particular record in the whole table.

  AGFC45824 - 9 digit emp. code

  S1572 or S15802 - 5 or 6 digit emp. code

  CSKW758104 - 10 digit emp. code

  The CASE_HISTORY column contains the following (there are multiple rows - one sample row as below):

  Case moved from Q of [AFDC15820]s to Q of [ADFV15870]s ...Case moved from to Q of [ADFV15870]s to Q of [S13572]s ... Case moved from Q of [S13572]s to [CSKW10072]s... Case moved from Q of [CSKW10072]s to Q of [S13572]s.. Case moved from Q of [S13572]s to Q of [ADFV15876]s ...

  I need to extract the ids found in the above column (for each record in my table) based on the conditions below.

  1. The employee code needs to searched and populated in this order only - REF_TO column, then REF_FROM column and finally CASE_HISTORY - The REF_TO, REF_FROM columns will have the latest owners (emp.code) of the record in his queue.

  2. In case, if any of the columns (in the above order) if the emp. code is S10353 (the system automatically pushes case from one user to another using this id), then i need to check the CASE_HISTORY column and extract the last owners REF_FROM and REF_TO.

  3. Also, if either of the columns REF_FROM, REF_TO contains the 9 digit emp. code, i need to ignore their values and pick out the last owners from CASE_HISTORY based on condition 2 above.

  If no employee code could be found, then populate it as empty

  I've coded these conditions using CASE .. WHEN block and PATINDEX, but i'm a bit struck-up and getting lost. :crazy:

  Can someone help me out to extract the correct employee codes?

  Will try posting the code that i've built shortly. I would need to scan & extract the employee code from this table, so i prefer this code to be built using a cursor.

  Hope Paul White takes a look at this post.. :w00t:

  Regards

  Sarang[/font]

• rangamails

  Old Hand

  Points: 331

  More actions

  May 19, 2012 at 12:00 am

  #1490080

  [font="Courier New"]Hi All,

  Here's the coding that i've done (attached to this post). Apologies for uploading the code in scanned format.

  Need to fix up the coding for the cursor (PENDING part found in page 3) in which i need to implement the logic to extract the employee code (logic mentioned in my initial post to this thread).

  Could someone help me out to fix the code in cursor part?

  Regards

  Sarang[/font]

• rangamails

  Old Hand

  Points: 331

  More actions

  May 22, 2012 at 10:03 am

  #1491185

  [font="Courier New"]Hi,

  Have added some cursor coding fo my requirement.. Need some help in adding functionality (using PATINDEX) to this code below:

  CREATE TABLE STAFF_CODE

  ( CASE_NO VARCHAR(11),

  ACCT_NO VARCHAR(19),

  TSE_CODE VARCHAR(20)

  )

  GO

  DECLARE @CASE_NBR VARCHAR(11), @ACCT_NBR VARCHAR(19), @REF_FROM VARCHAR(50), @REF_TO VARCHAR(50), @ACTION_TAKEN VARCHAR(50), @CASE_HISTORY VARCHAR(200), @CAT VARCHAR(50), @SUB_CAT VARCHAR(50), @TSE_CODE VARCHAR(25)

  DECLARE TSE_SM_CURSOR CURSOR FOR

  SELECT CASE_NO, ACCT_NO, REF_FR_USR, REF_TO_USR, ACTION_TAK, CASE_HISTORY, CAT, SUBCAT FROM RTO_CASE_UNQ WHERE SUBCAT LIKE '%CORPORATE_NEW%' ORDER BY CONVERT(DATETIME, CAST(LST_MOD_DT AS DATETIME), 103) AS LST_MOD_DT

  FOR READ ONLY

  OPEN TSE_SM_CURSOR

  FETCH NEXT FROM TSE_SM_CURSOR INTO @CASE_NBR, @ACCT_NBR, @REF_FROM, @REF_TO, @ACTION_TAKEN, @CASE_HISTORY, @CAT, @SUBCAT

  WHILE @FETCH_STATUS = 0

  BEGIN

  IF LTRIM(RTRIM(UPPER(@CASE_HISTORY))) NOT LIKE '%CASE HISTORY DETAILS TOO LONG TO BE DISPLAYED%' AND LTRIM(RTRIM(UPPER(@CASE_HISTORY))) NOT LIKE ' '

  BEGIN

  IF LTRIM(RTRIM(UPPER(@ACTION_TAKEN))) LIKE '%TAT%BUSTED%'

  BEGIN

  -- LOGIC TO BE ADDED TO EXTRACT THE STAFF CODE USING PATINDEX & ASSIGN IT TO @TSE_CODE

  -- SET @TSE_CODE =

  INSERT INTO STAFF_CODE VALUES(@CASE_NBR, @ACCT_NBR, @TSE_CODE)

  END

  END

  END

  CLOSE TSE_SM_CURSOR

  DEALLOCATE TSE_SM_CURSOR[/font]