String search

• ssc_san

  SSCommitted

  Points: 1563

  More actions

  April 14, 2012 at 4:44 pm

  #259313

  Hello all,

  I have an issue with string search from a free text field.

  --SAMPLE DATA

  CREATE TABLE #teststrser

  (

  ACT_LN VARCHAR(250),

  DOC_DSCR VARCHAR(250)

  )

  INSERT INTO #teststrser

  SELECT '14406 PPE 11/19/2011 FMS ABC 1234567 - Task Order - Reg Hours', '0000100313-Reg Hours-07/30/2011-Task Order'

  UNION ALL

  SELECT 'Task Order - EIN 0000127424 - Reg Hours- PPE 08/13/2011- ABC CDE 0000000','0000100313-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT '104406 PPE 11/19/2011 FMS ABC 1234567 - Task Order - Reg Hours', '0000100313-Reg Hours-07/30/2011-Task Order'

  UNION ALL

  SELECT null, '0000100230-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT '127830 PPE 11/19/2011 FMS ABC 1234567 - Task Order - Overtime Hours', 'ETAF 12-17-2011 TASK ORDER'

  UNION ALL

  SELECT '121161 PPE 11/19/2011 FMS CDE 4621212 - Task Order - Holiday worked Hours', 'ETAF 01-14-2012 TASK ORDER'

  UNION ALL

  SELECT 'EIN 122888 PPE 10/08/2011 FMS XYZ CD97003 - Task Order - Reg Hours', NULL

  UNION ALL

  SELECT 'EIN 105388 PPE 10/22/2011 FMS CDE 1234612 - Task Order - Holiday worked Hours', 'ETAF 12-31-2011 TASK ORDER'

  UNION ALL

  SELECT null, 'ETAF 02-25-2012 TASK ORDER'

  UNION ALL

  SELECT null, '0000100230-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT 'EIN 126616 PPE 01/28/2012 FMS CENTER 4610719 - Task Order - Reg Hours', 'Task Order - EIN 0000100230 - Reg Hours- PPE 08/13/2011- FMS ABC 4610115'

  UNION ALL

  SELECT 'EIN 125470 PPE12/03/2011 FMS ABC 4610504 - Task Order - Overtime Hours', 'Task Order - EIN 0000100230 - Reg Hours- PPE 08/13/2011- FMS ABC 4610115'

  UNION ALL

  SELECT 'EIN 125680 PPE 02/11/2012 FMS CENTER 4610110 - Task Order - Reg Hours', NULL

  UNION ALL

  SELECT 'EIN 106074 PPE12/03/2011 FMS ABC 4610504 - Task Order - Reg Hours', NULL

  UNION ALL

  SELECT 'EIN 125462 PPE 12/31/2011 FMS CENTER 4610506 - Task Order - Reg Hours', '0000100327-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT 'EIN 111179 PPE11/05/2011 FMS ABC 2920302 - Task Order - Reg Hours', '0000100327-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT 'EIN 120384 PPE12/03/2011 FMS ABC 1900203 - Task Order - Reg Hours', '0000100327-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT null, 'Task Order - EIN 0000100230 - Reg Hours- PPE 08/13/2011- FMS ABC 4610115'

  UNION ALL

  SELECT null, 'Task Order - EIN 0000100230 - Reg Hours- PPE 08/13/2011- FMS ABC 4610115'

  UNION ALL

  SELECT null, '0000100327-Reg Hours-07/16/2011-Task Order'

  UNION ALL

  SELECT 'EIN 125958 PPE 09/10/2011 FMS ABC 2320505 - Task Order - Reg Hours', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'EIN 120923 PPE 09/24/2011 FMS ABC 4610801 - Task Order - Reg Hours', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'EIN 107865 PPE12/03/2011 FMS ABC 2920308 - Task Order - Reg ', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'EIN 102029 PPE12/03/2011 FMS ABC 4610504 - Task Order - Reg Hours', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'EIN 104930 PPE 02/11/2012 FMS CENTER 4610504 - Task Order - Reg Hours', NULL

  UNION ALL

  SELECT 'EIN 126995 PPE12/03/2011 FMS ABC 2920302 - Task Order - Reg Hours', NULL

  UNION ALL

  SELECT 'EIN 113363 PPE 10/22/2011 FMS ABC 4610412 - Task Order - Reg Hours', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'Task Order - EIN 0000100188 - Reg Hours- PPE 08/13/2011- FMS ABC 1234567', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'Task Order - EIN 0000100188 - Reg Hours- PPE 08/13/2011- FMS ABC 1234567', NULL

  UNION ALL

  SELECT 'Task Order - EIN 0000100188 - Reg Hours- PPE 08/13/2011- FMS ABC 1234567', 'ETAF 11/19/2011 Task Order'

  UNION ALL

  SELECT 'Task Order - EIN 0000100188 - Reg Hours- PPE 08/13/2011- FMS ABC 1234567', '0000126522-Reg Hours-07/30/2011-Task Order'

  SELECT * FROM #teststrser

  --MY QUERY

  SELECT *,

  CASE WHEN ACT_LN IS NOT NULL THEN

  CASE WHEN CHARINDEX('EIN', ACT_LN, 1) > 10

  THEN RIGHT(LTRIM(RTRIM(SUBSTRING (ACT_LN, CHARINDEX('-', ACT_LN, 1) + 1, CHARINDEX('-', SUBSTRING(ACT_LN, CHARINDEX('-', ACT_LN, 1) + 1, LEN(ACT_LN)), 1) - 1))), 6)

  WHEN CHARINDEX('EIN', ACT_LN, 1) = 0

  THEN RTRIM(LTRIM(LEFT(ACT_LN, 6)))

  WHEN CHARINDEX('EIN', ACT_LN, 1) = 1

  THEN SUBSTRING(ACT_LN, CHARINDEX('EIN', ACT_LN, 1)+4, 6)

  ELSE NULL

  END

  WHEN ACT_LN IS NULL THEN

  CASE WHEN DOC_DSCR IS NULL THEN 'N/A'

  WHEN CHARINDEX('ETAF', DOC_DSCR, 1) > 0 THEN 'NO EIN FOUND'

  WHEN CHARINDEX('EIN', DOC_DSCR, 1) > 10

  THEN RIGHT(LTRIM(RTRIM(SUBSTRING (DOC_DSCR, CHARINDEX('-', DOC_DSCR, 1) + 1, CHARINDEX('-', SUBSTRING(DOC_DSCR, CHARINDEX('-', DOC_DSCR, 1) + 1, LEN(DOC_DSCR)), 1) - 1))), 6)

  ELSE RIGHT(SUBSTRING(DOC_DSCR, 1, CHARINDEX('-', DOC_DSCR, 1) - 1), 6)

  END

  ENDAS EIN

  FROM #teststrser

  DROP TABLE #teststrser

  The issue here is, I am searching for the a 6 digit number followed by EIN or if it is at the beginning of the field I am parsing out that field for the EIN #.

  there is a case for which i need help, there are chances the users may enter only five digits at the beginning of the field, in that case, I have append a "zero" in front of the extracted 5 digit number.

  Please let me know any tweaks to the query I wrote to work efficiently.

  Thanks in advance!

• April 16, 2012 at 1:57 am

  This was removed by the editor as SPAM

• ssc_san

  SSCommitted

  Points: 1563

  More actions

  April 16, 2012 at 6:50 am

  #1473992

  Thanks for the reply Stewart!

  I have to test and make sure, I don't have any other cases that are missing.

  Thanks again!