Substring with Charindex or Patindex and case statement

• sfletcher-933270

  Right there with Babe

  Points: 784

  More actions

  November 10, 2010 at 10:22 pm

  #231230

  I would like to clean up some of our data using substring and charindex/ patindex with a case statement to strip out ship name prefixes such as USS and US and USN from the Terms field, as per the two scripts below. However, I encounter problems with my use of the charindex function and end up with new prefixes that do not reflect what I expecting.

  I have included some sample data and two codes below.

  Grateful for any assistance you can give me as I clearly am missing something with the Charindex/Patindex and substring thing.

  Apologies - when looking at this in the preview I can see that my data seems to muck up the delimiters forthe insert script. It does not appear as it does in SQL server management studio where it executes correctly.

  Kind regards, Sally

  --Sample data

  /*Please note - I tried to follow the steps set out in the posting to theforum etiquette article but

  had trouble with the identity insert so I didn't include this statement when inserting to my temp table

  */

  SET IDENTITY_INSERT #TempObjects_SF ON

  Insert into #TempObjects_SF (TermID, Path, CN, Term)

  SELECT '2039644','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OXW','YACHT "AUSTRALIA" [III]' UNION ALL

  SELECT '2033428','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FUH','YACHT "AUSTRALIA" [II].' UNION ALL

  SELECT '2033428','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FUH','YACHT "AUSTRALIA" [II].' UNION ALL

  SELECT '2033428','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FUH','YACHT "AUSTRALIA" [II].' UNION ALL

  SELECT '2033428','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FUH','YACHT "AUSTRALIA" [II].' UNION ALL

  SELECT '2033428','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FUH','YACHT "AUSTRALIA" [II].' UNION ALL

  SELECT '2034325','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HCM','WHEELHOUSE' UNION ALL

  SELECT '2034325','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HCM','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2032573','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ENW','WHEELHOUSE' UNION ALL

  SELECT '2038007','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.MNG','WEST AUSTRALIAN SN CO LTD' UNION ALL

  SELECT '2038007','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.MNG','WEST AUSTRALIAN SN CO LTD' UNION ALL

  SELECT '2034896','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HYI','VICTORIAN COLONIAL NAVY[AUS]' UNION ALL

  SELECT '2117655','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.TKC','USS Ships' UNION ALL

  SELECT '2033482','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FWJ','USS "TRENTON"' UNION ALL

  SELECT '2038425','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.NDH','USS "SEATTLE"' UNION ALL

  SELECT '2033587','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GAJ','USS "PENNSYLVANIA"' UNION ALL

  SELECT '2033587','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GAJ','USS "PENNSYLVANIA"' UNION ALL

  SELECT '2033587','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GAJ','USS "PENNSYLVANIA"' UNION ALL

  SELECT '2027528','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.AEQ','USS "PEARY"' UNION ALL

  SELECT '2041548','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.RSS','USS "PATEENA"' UNION ALL

  SELECT '2032425','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EIE','USS "OMAHA"' UNION ALL

  SELECT '2030296','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BEW','USS "OKLAHOMA"' UNION ALL

  SELECT '2030296','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BEW','USS "OKLAHOMA"' UNION ALL

  SELECT '2030296','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BEW','USS "OKLAHOMA"' UNION ALL

  SELECT '2030296','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BEW','USS "OKLAHOMA"' UNION ALL

  SELECT '2032371','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EGC','USS "NEW MEXICO"' UNION ALL

  SELECT '2031233','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.COR','USS "NEVADA"' UNION ALL

  SELECT '2031233','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.COR','USS "NEVADA"' UNION ALL

  SELECT '2041322','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.RKB','USS "MINNIAPOLIS"' UNION ALL

  SELECT '2036395','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.KDR','USS "CONSTITUTION"' UNION ALL

  SELECT '2036395','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.KDR','USS "CONSTITUTION"' UNION ALL

  SELECT '2033763','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GGZ','USS "COLORADO" [?]' UNION ALL

  SELECT '2041981','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.SJB','USS "CALIFORNIA"' UNION ALL

  SELECT '2033679','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GDV','USS "BROOKLYN"' UNION ALL

  SELECT '2037875','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.MIE','USS "AUGUSTA"' UNION ALL

  SELECT '2031684','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DFV','USS "ARIZONA"' UNION ALL

  SELECT '2032468','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EJV','USS "ALABAMA" DESTRUCTION' UNION ALL

  SELECT '2039919','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.PIK','USN SUBMARINE "HALIBUT"' UNION ALL

  SELECT '2037965','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.MLQ','USHS "RELIEF"' UNION ALL

  SELECT '2027522','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.AEK','USAT "DAVID C SHANKS"' UNION ALL

  SELECT '2031796','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DKC','USA GOVERNMENT' UNION ALL

  SELECT '2033204','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FLU','USA BATTLESHIP "NEW JERSEY"' UNION ALL

  SELECT '2038009','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.MNI','USA BATTLESHIP "ILLINOIS"(?)' UNION ALL

  SELECT '2038400','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.NCI','US SHIP "ALABAMAN"' UNION ALL

  SELECT '2034308','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HBV','US MARITIME COMMISSION' UNION ALL

  SELECT '2039490','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.ORY','US DESTROYER "JOHN W WEEKS"' UNION ALL

  SELECT '2034373','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HEI','US BATTLESHIP "KEARSAGE"' UNION ALL

  SELECT '2032393','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EGY','US BATTLESHIP "CALIFORNIA"' UNION ALL

  SELECT '2039974','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.PKN','US "SEATTLE": US"PENNSYLVANIA"' UNION ALL

  SELECT '2032548','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EMX','US "NEBRASKA"' UNION ALL

  SELECT '2033795','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GIE','U.S.S."VERMONT"' UNION ALL

  SELECT '2041462','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.RPL','U.S.S."TEXAS".' UNION ALL

  SELECT '2033740','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.GGD','U.S.S."SEATTLE".' UNION ALL

  SELECT '2038831','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.NST','U.S.S."NEBRASKA".' UNION ALL

  SELECT '2032370','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EGB','U.S.S."MARYLAND": "BRIDGER".' UNION ALL

  SELECT '2039273','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OJR','U.S.S."LANGLEY".' UNION ALL

  SELECT '2039032','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OAM','U.S.S. "VIRGINIA".' UNION ALL

  SELECT '2033362','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.FRV','U.S.S. "TRENTON: S.S."MEDUSA' UNION ALL

  SELECT '2032808','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EWT','U.S.S. "PENNSYLVANIA".' UNION ALL

  SELECT '2039660','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OYM','U.S.S. "NEW MEXICO".' UNION ALL

  SELECT '2036974','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.KZS','U.S.S. "NEVADA".' UNION ALL

  SELECT '2032543','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EMS','U.S.S. "MISSISSIPPI".' UNION ALL

  SELECT '2034715','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HRJ','U.S.S. "MAC DONOUGH"' UNION ALL

  SELECT '2039293','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OKL','U.S.S. "LOUISIANA".' UNION ALL

  SELECT '2042048','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.SLP','U.S.S. "IDAHO".' UNION ALL

  SELECT '2031320','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.CRZ','U.S.S. "CALIFORNIA".' UNION ALL

  SELECT '2034893','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.HYF','U.S.N. "NEBRASKA"' UNION ALL

  SELECT '2037391','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.LPQ','U.S. CRUISER "ASTORIA"' UNION ALL

  SELECT '2039282','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OKA','U.S. BATTLESHIPS "MAINE"' UNION ALL

  SELECT '2031973','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DQX','TSS "CHUSAN"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2032187','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DZB','TSMV "EMPRESS OF AUSTRALIA"' UNION ALL

  SELECT '2039315','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.OLH','TSMV "AUSTRALIAN TRADER"' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2030407','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.BJD','TRINITY HOUSE' UNION ALL

  SELECT '2031763','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.DIV','TRAILERABLE HOUSEBOAT' UNION ALL

  SELECT '2032294','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EDD','TOYO KISEN KABUSHIKI KAISHA' UNION ALL

  SELECT '2032294','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EDD','TOYO KISEN KABUSHIKI KAISHA' UNION ALL

  SELECT '2032294','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.EDD','TOYO KISEN KABUSHIKI KAISHA' UNION ALL

  SELECT '2037114','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.LFA','TNT "EXPRESS AUGUST"' UNION ALL

  SELECT '2031147','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.CLK','THE BLUE WATER BUSHMEN #' UNION ALL

  SELECT '2031147','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.CLK','THE BLUE WATER BUSHMEN #' UNION ALL

  SELECT '2031147','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.CLK','THE BLUE WATER BUSHMEN #' UNION ALL

  SELECT '2027420','Authorities\Attributes\Objects\Subject terms\','AUT.AAA.AAG.AAA.AAM','THE AUSTRALIAN WAR MEMORIAL'

  --SET IDENTITY_INSERT #TempObjects_SF on

  --==========================================================================

  --Code 1

  --My script creates the NewTerm and Suffix correctly

  --Problems with the Prefix which is giving me a prefix of US where the CharIndex 'US' is found in Australia

  --I want to restrict it to the Charindex at the beginning of the column, eg US "NEBRASKA" (row 56)

  --Also don't understand why my script doesn't work in row 73 - U.S.N. "NEBRASKA"NEBRASKAUS

  select * ,

  case when charindex ('"',(substring(Term,charindex('"',Term,1)+1,99))) = 0

  then ltrim(rtrim(substring(Term,charindex('"',Term,1)+1,99)))

  else ltrim(rtrim(substring(Term, charindex('"',Term,1) + 1, charindex('"', substring(Term, charindex('"', Term, 1) + 1, 99)) - 1

  )))

  end as NewTerm,

  casewhen patindex ('%U.S.S.%',Term)>0

  then 'USS'

  when patindex('%U.S S.%',Term) >0

  then 'USS'

  when patindex ('%U.S.%',Term) >0

  then 'US'

  when patindex ('%U.S.N.%',Term) >0

  then 'USN'

  when patindex ('%USS%',Term)>0

  then 'USS'

  when patindex ('%US%',Term)>0

  then 'US'

  when patindex ('%U.S.N.%',Term) >0

  then 'USN'

  when patindex ('%USAT%',Term) >0

  then 'USAT'

  when patindex ('%USHS%',Term) >0

  then 'USHS'

  when patindex ('%USHS.%',Term) >0

  then 'USHS'

  when patindex ('%USAT%',Term) >0

  then 'USAT'

  when patindex('%USN%',Term) >0

  then 'USN'

  when patindex('%USS%',Term)>0

  then 'USS'

  when patindex('%USA%',Term)>0

  then 'USA'

  end as Prefix,

  casewhen (CHARINDEX('[',Term) >0and charindex (']', Term)>0)

  then substring (Term, (charindex('[', Term)+1), ((charindex(']',Term)-(charindex('[', Term))-1) ) )

  end as Suffix

  from #TempObjects_SF

  --------------------------------------------------------------------------------------

  --Code 2

  --In this script I was trying to make a new term that included the prefix

  --Seemed to work ok but not consistent for example USS "PEARY"US PEARY in row 25 - I would expect to see USS PEARY here not US PEARY

  select*,

  CASE WHEN CHARINDEX('"',(SUBSTRING(Term,CHARINDEX('"',Term,1)+1,99))) = 0

  THEN LTRIM(RTRIM(SUBSTRING(Term,CHARINDEX('"',Term,1)+1,99)))

  /* sample only of records - these with potential prefixes starting with U*/

  when charindex ('U.S S.',Term) >0

  then 'USS' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('U.S.',Term) >0

  then 'US' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('U.S.N.',Term) >0

  then 'USN' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('U.S.S.',Term)>0

  then 'USS' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex('US',Term) =1--

  then 'US' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('U.S.N.',Term) >0

  then 'USN' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('USAT',Term) >0

  then 'USAT' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex('USHS.',Term) >0

  then 'USHS' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('USN',Term) >0

  then 'USN' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  when charindex ('USS',Term)>0

  then 'USS' + ' ' + LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1,CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1 )))

  ELSE LTRIM(RTRIM(SUBSTRING(Term, CHARINDEX('"',Term,1) + 1, CHARINDEX('"', SUBSTRING(Term, CHARINDEX('"', Term, 1) + 1, 99)) - 1

  ) ) )

  end as NewTerm,

  casewhen (CHARINDEX('[',Term) >0and charindex (']', Term)>0)

  then substring (Term, (charindex('[', Term)+1), ((charindex(']',Term)-(charindex('[', Term))-1) ) )

  end as Suffix

  from #TempObjects_SF

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 11, 2010 at 7:40 am

  #1249507

  Sally,

  Two things that I've noticed in looking over this:

  1. The order that you evaluate things is important. Looking for "U.S." before "U.S.N." will always find the match on U.S., and not on U.S.N. You need to move the U.S.N. above the U.S.

  2. You might want to change the prefix matching to not include the beginning wildcard - otherwise, names like "YACHT AUSTRALIA [III]" is finding a match on "US".

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• sfletcher-933270

  Right there with Babe

  Points: 784

  More actions

  November 11, 2010 at 3:40 pm

  #1249758

  Wayne

  Thanks so much for your response which has worked to give me the correct prefixes in my new terms.

  Can you please tell me how to change the prefix matching as per your second suggestion? I am keen to learn as much as possible and this might be useful to me in the future.

  2. You might want to change the prefix matching to not include the beginning wildcard - otherwise, names like "YACHT AUSTRALIA [III]" is finding a match on "US".

  Kind regards, Sally

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 11, 2010 at 9:23 pm

  #1249805

  Well, as I mentioned, "YACHT AUSTRALIA [III]" is finding a match on "US". This is because you are using this bit of code: when patindex ('%US%',Term)>0

  then 'US'

  The "%" is a wild card, so it is saying to find the phrase "US" anywhere in the expression being searched. Remove the leading "%", and it will now find a match only on the expressions what start with "US".

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• sfletcher-933270

  Right there with Babe

  Points: 784

  More actions

  November 14, 2010 at 2:29 pm

  #1250470

  Thank you very much for responding again Wayne. That is obviously such a simple solution!

  Kind regards, Sally