Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring with Charindex or Patindex and case statement Expand / Collapse
Author
Message
Posted Wednesday, November 10, 2010 10:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:28 AM
Points: 16, Visits: 108
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" NEBRASKA US

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,

case when 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,

case when (CHARINDEX('[',Term) >0 and 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,

case when (CHARINDEX('[',Term) >0 and charindex (']', Term)>0)
then substring (Term, (charindex('[', Term)+1), ((charindex(']',Term)-(charindex('[', Term))-1) ) )

end as Suffix

from #TempObjects_SF

Post #1019059
Posted Thursday, November 11, 2010 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 6,582, Visits: 8,859
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019345
Posted Thursday, November 11, 2010 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:28 AM
Points: 16, Visits: 108
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

Post #1019627
Posted Thursday, November 11, 2010 9:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 6,582, Visits: 8,859
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1019686
Posted Sunday, November 14, 2010 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:28 AM
Points: 16, Visits: 108
Thank you very much for responding again Wayne. That is obviously such a simple solution!

Kind regards, Sally
Post #1020479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse