Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Substring with Charindex or Patindex and case statement


Substring with Charindex or Patindex and case statement

Author
Message
sfletcher-933270
sfletcher-933270
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 218
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


WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6255 Visits: 10403
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, 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

sfletcher-933270
sfletcher-933270
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 218
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
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6255 Visits: 10403
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, 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

sfletcher-933270
sfletcher-933270
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 218
Thank you very much for responding again Wayne. That is obviously such a simple solution!

Kind regards, Sally
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search