Matching characters within a string

I'd like to match the first n characters in column a to the first n characters of an ID in column b, where column b can have multiple groupings of IDs in a single column. Each grouping of IDs can have a varying number of IDs.

Each grouping of IDs is separated by a comma. Each ID within a grouping is enclosed in quotes. Each ID within a grouping is separated with a bar ( | ) .

Column a
AA-
BB-
CC-
DD-

column B
"| AA-bbccdd | BB-gghhjj | CC-qqwwee |", "| AA-ssddff |"

"| FG-rrttyyuu |","| DD-nnmmuu | FF-yyuuii |", "| AA-ddxxff | BB-aassdd | CC-qqwwee |"

I only need to know if there's a match on the first n characters of the first n ids within each grouping. Thank you!

This might get you started:

select *
  from yourtable
 where charindex('|'+[column a],','+replace(replace(replace([column b],' ',''),'"',''),',|',''))>0
;
1 Like