How to search a like statement with expanding characters

user_13112202 0
Viewed 48

I have a list of sql objects from the sys tables, let's say about 14,000 of them, that I need to determine what team should be assigned to review them for a data normalization project. We will also be implementing a correct naming convention moving forward. Currently, it has not been implemented correctly, thus causing the issue I am facing.

Given the following example:

drop table if exists #origin;

create table #origin ( objectName varchar (40) not null );

insert into #origin ( objectName )
values ( 'FoTacoTruck' )
     , ( 'FooHappyForest' )
     , ( 'gseBAN' )
     , ( 'gse_dt_Primer' )
     , ( 'gse_sparkles' );

drop table if exists #assignments;

create table #assignments ( objectPrefix varchar (10) not null, team_name varchar (20) not null );

insert into #assignments ( objectPrefix, teamName )
values ( 'Fo', 'Team Edward' )
     , ( 'Foo', 'Team Jacob' )
     , ( 'gse', 'Team Steve' )
     , ( 'gse_dt', 'Team Sabrina' );

select o.objectname
     , a.team_name
from #assignments a
join #origin      o on o.objectName like ( concat( a.objectPrefix, '%' ));

Currently, this yields the following:

objectName     | teamName
=================================
FoTacoTruck    | Team Edward
FooHappyForest | Team Edward
FooHappyForest | Team Jacob
gseBan         | Team Steve
gse_dt_Primer  | Team Steve
gse_sparkles   | Team Steve
gse_dt_Primer  | Team Sabrina

I am having a very difficult time figuring out how to get the names to work due to the use of a wildcard. Currently, the objects are not all using an ObjectPreFix_ designation, which they will be in the future, allowing me to use charindex or regex to parse.

I can't substring because there isn't always the right amount of characters... I can't use charindex because there isn't always a delimiter to reference...I haven't figured out how to use any patindex, yet, but think that might be part of the key. And, unfortunately, I can't even figure out what to google to help see if there are other questions like this.

What I have control over:

  • The #assignments table
  • The query used to assign

What I do NOT have control over:

  • the #origin table since it is based completely off object names in the sys tables and is just presented as a temp table here to simplify the code

Tl;DR: How do I get the objectPrefix to match up with the objectName when there is no discernible (by me) pattern?

ssms sql-server-2016 tsql sql-server
1 Answers

Seems like you've got an implicit rule here: the more detailed (a.k.a. longer) the prefix, the higher priority it should have in matching it with a team name. So match as you have been doing, but when there are multiple matches, take the one that was matched using the longest prefix:

with prefixMatches as (

    select      o.objectname, 
                a.team_name,
                ord = row_number() over(
                    partition by o.objectName 
                    order by len(a.objectPrefix) desc
                )
    from        #assignments a
    join        #origin o on o.objectName like a.objectPrefix + '%'

)

select      objectName,
            team_name
from        prefixMatches
where       ord = 1
user_34165510
Related