Column Contains Character From Another Column

Is there an easy way to have a condition "where column A has a character from a column B character"? Basically I have a column that has a letter for each day of the work week; MTWHF. I need to join two records where the days match, which is basically if letters match up in the strings.

----------------
| ID | MetDays |
----------------
| 1 | 'MWF' |
| 2 | 'TH' |
| 3 | 'M' |
| 4 | 'T' |
| 5 | 'WHF' |
----------------


The SQL query would be something like;

SELECT MyTableA.ID AS IDa, MyTableB.ID AS IDb
FROM MyTable AS MyTableA
JOIN MyTable AS MyTableB
ON MyTableA.MetDays ???? MyTableB.MetDays


In this case, I would have successful JOIN between;

JOIN

-------------
| IDa | IDb |
-------------
| 1 | 3 |
| 1 | 5 |
| 2 | 4 |
| 2 | 5 |
| (reverse) |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 5 | 2 |
-------------


3 Answers
3

Here's one approach splitting the days into 5 separate columns using substring:

substring

select t1.id as IDa, t2.id as IDb
from mytable t1, mytable t2
where t1.id != t2.id and
( (t1.metdays like '%' + substring(t2.metdays,1,1) + '%' and substring(t2.metdays,1,1) != '')
or (t1.metdays like '%' + substring(t2.metdays,2,1) + '%' and substring(t2.metdays,2,1) != '')
or (t1.metdays like '%' + substring(t2.metdays,3,1) + '%' and substring(t2.metdays,3,1) != '')
or (t1.metdays like '%' + substring(t2.metdays,4,1) + '%' and substring(t2.metdays,4,1) != '')
or (t1.metdays like '%' + substring(t2.metdays,5,1) + '%' and substring(t2.metdays,5,1) != '')
)
order by t1.id, t2.id


t1.metdays like '%' + nullif(substring(t2.metdays,1,1), '') + '%'

create table MyTable ( ID int, MetDays varchar(5) )

insert into MyTable ( ID, MetDays ) values
( 1, 'MWF' ),
( 2, 'TH' ),
( 3, 'M' ),
( 4, 'T' ),
( 5, 'WHF' )


;with

-- Create a table of the 5 characters.
-- You might want to make this a permanent table.

DayList as
( select 'M' as aDay
union select 'T'
union select 'W'
union select 'H'
union select 'F' ),

-- Join MyTable with this list.
-- The result will be one record for each letter in each row
-- ID aDay
-- 1 M
-- 1 W
-- 1 F
-- and so on

MetDayList as
( select ID, aDay
from MyTable
join DayList
on MyTable.MetDays like '%' + aDay + '%' )

-- Self join this table

select distinct A.ID as IDa, B.ID as IDb
from MetDayList A
join MetDayList B
on A.ID <> B.ID
and A.aDay=B.aDay
order by IDa, IDb


The following approach dispenses with like and uses bitmasks.

like

CharIndex is used to determine if a specific letter is in a string. It returns either the one-based position of the character or zero. Sign is used to fold all positive values to 1 while passing 0 through. With suitable multipliers (1, 2, 4, 8, 16) the matches are assembled into a bitmask with the least-significant-bit (LSB) being Monday, ... .

CharIndex

Sign

1

0

Bitwise arithmetic can be used on bitmasks. A bitwise AND will return all of the bits which are set (1) in both arguments. If there are no set bits in common then the result will be zero.

1

The process can be reversed to convert a bitmask to a string of day letters.

For improved performance the bitmasks can be stored in a persisted computed column. Note that indexing is not likely to be very helpful.

-- Sample data.
declare @Meetings as Table ( Id Int Identity, MetDays VarChar(5) );
insert into @Meetings ( MetDays ) values
( 'MWF' ), ( 'TH' ), ( 'M' ), ( 'T' ), ( 'WHF' );
select * from @Meetings;

-- Play with the data.
declare @BusyDays as VarChar(5) = 'MWF'; -- I'm busy these days.
with
BusyDays as ( -- Build a bitmask of the days that I'm busy.
select @BusyDays as BusyDays,
Sign( CharIndex( 'M', @BusyDays ) ) +
Sign( CharIndex( 'T', @BusyDays ) ) * 2 +
Sign( CharIndex( 'W', @BusyDays ) ) * 4 +
Sign( CharIndex( 'H', @BusyDays ) ) * 8 +
Sign( CharIndex( 'F', @BusyDays ) ) * 16 as BusyDaysBitMask ),
MetDays as ( -- Build a bitmask for the days each meeting occurs.
select MetDays,
Sign( CharIndex( 'M', MetDays ) ) +
Sign( CharIndex( 'T', MetDays ) ) * 2 +
Sign( CharIndex( 'W', MetDays ) ) * 4 +
Sign( CharIndex( 'H', MetDays ) ) * 8 +
Sign( CharIndex( 'F', MetDays ) ) * 16 as MetDaysBitMask
from @Meetings )
select MD.MetDays, MD.MetDaysBitMask, BD.BusyDays, BD.BusyDaysBitMask,
-- Bitwise AND of day bitmasks. Zero means no days in common.
MD.MetDaysBitMask & BD.BusyDaysBitMask as CollisionDaysBitMask,
CD.CollisionDays
from BusyDays as BD cross join
MetDays as MD cross apply
( select -- Convert the collision bitmask back to a set of day letters.
case when MD.MetDaysBitMask & BD.BusyDaysBitMask & 1 != 0 then 'M' else '' end +
case when MD.MetDaysBitMask & BD.BusyDaysBitMask & 2 != 0 then 'T' else '' end +
case when MD.MetDaysBitMask & BD.BusyDaysBitMask & 4 != 0 then 'W' else '' end +
case when MD.MetDaysBitMask & BD.BusyDaysBitMask & 8 != 0 then 'H' else '' end +
case when MD.MetDaysBitMask & BD.BusyDaysBitMask & 16 != 0 then 'F' else '' end as
CollisionDays ) CD;


To compare meetings for days in common:

with
MetDays as ( -- Build a bitmask for the days each meeting occurs.
select MetDays,
Sign( CharIndex( 'M', MetDays ) ) +
Sign( CharIndex( 'T', MetDays ) ) * 2 +
Sign( CharIndex( 'W', MetDays ) ) * 4 +
Sign( CharIndex( 'H', MetDays ) ) * 8 +
Sign( CharIndex( 'F', MetDays ) ) * 16 as MetDaysBitMask
from @Meetings )
select MDL.MetDays as 'MetDays Left', MDL.MetDaysBitMask as 'MetDaysBitMask Left',
MDR.MetDays as 'MetDays Right', MDR.MetDaysBitMask as 'MetDaysBitMask Right',
-- Bitwise AND of day bitmasks. Zero means no days in common.
MDL.MetDaysBitMask & MDR.MetDaysBitMask as CollisionDaysBitMask,
CD.CollisionDays
from MetDays as MDL cross join
MetDays as MDR cross apply
( select -- Convert the collision bitmask back to a set of day letters.
case when MDL.MetDaysBitMask & MDR.MetDaysBitMask & 1 != 0 then 'M' else '' end +
case when MDL.MetDaysBitMask & MDR.MetDaysBitMask & 2 != 0 then 'T' else '' end +
case when MDL.MetDaysBitMask & MDR.MetDaysBitMask & 4 != 0 then 'W' else '' end +
case when MDL.MetDaysBitMask & MDR.MetDaysBitMask & 8 != 0 then 'H' else '' end +
case when MDL.MetDaysBitMask & MDR.MetDaysBitMask & 16 != 0 then 'F' else '' end as
CollisionDays ) CD;


If the letters will always be in the same order then a small lookup table can be used to map all 32 combinations of weekdays to the corresponding bitmask values. A somewhat larger table could be used if the order of letters is not guaranteed. That would replace all of the string manipulation and arithmetic with a table lookup in either direction.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.