April 13, 2010 at 10:59 am
I have a a table with a column of items, say: ALPHA_12, BETA_GAMMA_25, DELTA_PRODUCTION, TXEPSILON...
I have a union query of items, say: ALPHA, BETA_GAMMA, DELTA, EPSILON, ZETA, ETA...
I would like to find if there is a unique substring patternmatch of an item in the second list, within the first list, and update a field in the first table with the name of the matched item. If there is 0 or more than 1 match no update would be performed.
Is there any way to do this shy of using a cursor?
Thank you.
April 13, 2010 at 11:09 am
April 13, 2010 at 11:54 am
Yes, this can be done fairly easily, though efficiency may be an issue.
If you provide the information the bteraberry indicated, we can provide a working example for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 12:15 pm
Like this?
create table #myTableOfProjectInstances(ProjectInstanceName varchar(1000), CorrespondingProjectName varchar(100))
insert into #myTableOfProjectInstances(ProjectInstanceName) values ('ALPHA_12')
insert into #myTableOfProjectInstances(ProjectInstanceName) values ('BETA_GAMMA_25')
insert into #myTableOfProjectInstances(ProjectInstanceName) values ('DELTA_PRODUCTION')
insert into #myTableOfProjectInstances(ProjectInstanceName) values ('TXEPSILON')
insert into #myTableOfProjectInstances(ProjectInstanceName) values ('ALPHA_26')
create table #myTableOfProjectsByName(ProjectName varchar(100))
insert into #myTableOfProjectsByName
select 'ALPHA'
insert into #myTableOfProjectsByName
select 'DELTA'
insert into #myTableOfProjectsByName
select 'EPSILON'
insert into #myTableOfProjectsByName
select 'ZETA'
create table #myTableOfProjectsByCodeName(ProjectName varchar(100))
insert into #myTableOfProjectsByCodeName
select 'BETA_GAMMA'
insert into #myTableOfProjectsByCodeName
select 'RHO'
--find unique matches in this resultset
select * from #myTableOfProjectsByName
union
select * from #myTableOfProjectsByCodeName
--that patternmatch against
select ProjectInstaceName from #myTableOfProjectInstances
--and update CorrespondingProjectName field with ProjectName
drop table #myTableOfProjectsByCodeName
drop table #myTableOfProjectsByName
drop table #myTableOfProjectInstances
April 13, 2010 at 12:43 pm
That's progress, but I'm still not quite sure what you're looking for. So you only want to do an update if exactly one of the names is found within the string? So if both 'ALPHA' and 'DELTA' are found in the same string you don't want to update? Are the names you're searching for ('ALPHA', etc.) contained in a static list or does it need to be dynamic?
April 13, 2010 at 12:59 pm
Yes. It should only update if there is exactly one match (eg. ZETA_RHO_23 could match ZETA and RHO, therefore no update, KATHUMPA_GORGANZOLA_6, no match, no update) and the list needs to be dynamically constructed from the two temp tables.
Basically we have the proper project name, and the code name, and developers will create instances of these projects on a server using one or the other. The Project Manager for the project needs to keep the developers in line so there aren't 50 copies of the same project. I'm building a list of similar projects and notifying the project manager of these project instances, and the size.
If the project instance isn't named in accordance with the code name, or isn't sufficiently unique then it will need to be handled manually.
April 13, 2010 at 1:59 pm
This should do it:
;WITH ctePatterns AS
(
SELECT ProjectName as Pattern
FROM #myTableOfProjectsByName
UNION --note: if you know that there are no duplicates, add "ALL" for performance
SELECT ProjectName
FROM #myTableOfProjectsByCodeName
)
, cteSingleInstances AS
(
SELECT
ProjectInstanceName,
CorrespondingProjectName,
Pattern
FROM #myTableOfProjectInstances
JOIN ctePatterns pt ON 1 = (
SELECT COUNT(*)
FROM ctePatterns as pt2
WHERE ProjectInstanceName LIKE '%' + pt2.Pattern + '%'
)
AND ProjectInstanceName LIKE '%' + pt.Pattern + '%'
)
UPDATE cteSingleInstances
SET CorrespondingProjectName = Pattern
WHERE Pattern IS NOT NULL
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 2:26 pm
Thank you so much! And I learned about common table expressions today. 😉
I love SQL Server Central!
April 13, 2010 at 4:33 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 18, 2010 at 10:58 pm
A CTE-less alternative:
UPDATE Grouped
SET CorrespondingProjectName = Grouped.ProjectInstanceName
FROM (
SELECT Instances.ProjectInstanceName,
Instances.CorrespondingProjectName,
Unioned.ProjectName,
group_count = COUNT(*) OVER (PARTITION BY Instances.ProjectInstanceName)
FROM #myTableOfProjectInstances Instances
JOIN (
SELECT N.ProjectName
FROM #myTableOfProjectsByName N
UNION
SELECT CN.ProjectName
FROM #myTableOfProjectsByCodeName CN
) Unioned
ON CHARINDEX(Unioned.ProjectName, Instances.ProjectInstanceName) > 0
) Grouped
WHERE Grouped.group_count = 1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply