How to join a table by a column with tilde separated values

Question

I'm trying to match the codes/descriptions from table_2 to each company in table_1. The company_type_string column contains multiple codes separated by ~ that are supposed to match with the codes in table_2.

Table 1:

company    company_type_string
------------------------------
  A         1A~2B~3C
  B         1A~2B
  C         1A
  D         1A~2B~3C~4D

Table 2:

code    description
-----------------------
 1A     Finance
 2B     Law
 3C     Security
 4D     Marketing

Desired output:

company description
----------------------
   A    Finance
   A    Law
   A    Security
   B    Finance
   B    Law
   C    Finance
   D    Finance
   D    Law
   D    Security
   D    Marketing

I've tried using split_string with no success. Is there a way to make this join without altering the DB schema?

`split_string` is one possible answer, so we can't help you further without seeing exactly what you tried, so we can help understand why it didn't work for you and how to correct it. — Tab Alleman, Mar 26 '19 at 20:02
There's also this duplicate: https://stackoverflow.com/questions/26236436/joining-a-table-based-on-comma-separated-values — Tab Alleman, Mar 26 '19 at 20:06
Why can you not correct the schema? From a database design perspective, `company_type_string` is a bad idea in just about every possible way. — Brian, Mar 26 '19 at 20:18
Possible duplicate of [Joining a table based on comma separated values](https://stackoverflow.com/questions/26236436/joining-a-table-based-on-comma-separated-values) — Clay, Mar 26 '19 at 21:25
This is (obviously) a(n easily found) faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. [ask] — philipxy, Mar 27 '19 at 01:18
Possible duplicate of [join comma delimited data column](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column) — philipxy, Mar 27 '19 at 01:20

score 1 · Answer 1 · answered Mar 26 '19 at 20:25

1

If you can not fixed the schema, the following query should work:

   SELECT c.company, ct.description
   FROM table_1 c
   CROSS APPLY STRING_SPLIT(c.company_type_string,'~') t
   INNER JOIN table_2 ct
   ON t.value=ct.code;

answered Mar 26 '19 at 20:25

PeterHe

2,766
1
8
7

score 0 · Answer 2 · answered Mar 26 '19 at 20:25

I don't have 2016 yet myself so here is a way you can do this in earlier versions. This is using the DelimitedSplit8K function which you can find here. But the best possible solution would be to normalize your design.

declare @Company table
(
    company char(1)
    , company_type_string varchar(100)
)

insert @Company values
('A', '1A~2B~3C')
, ('B', '1A~2B')
, ('C', '1A')
, ('D', '1A~2B~3C~4D')

declare @Code table
(
    code char(2)
    , description varchar(50)
)

insert @Code values
('1A', 'Finance')
, ('2B', 'Law')
, ('3C', 'Security')
, ('4D', 'Marketing')

select c.company
    , co.description
from @Company c
cross apply dbo.DelimitedSplit8K(c.company_type_string, '~') x
join @Code co on co.code = x.Item

score 0 · Accepted Answer · answered Mar 26 '19 at 21:16

Here u have a code sample, the last part is the important for you, is pretty simple, easy to use and memorize.

IF OBJECT_ID('tempdb..#table_1') IS NOT NULL
    DROP TABLE #table_1
CREATE TABLE #table_1
(
company VARCHAR(1),
company_type_string VARCHAR(max)
)

INSERT INTO #table_1 VALUES
('A','1A~2B~3C'),
('B','1A~2B'),
('C','1A'),
('D','1A~2B~3C~4D')

IF OBJECT_ID('tempdb..#table_2') IS NOT NULL
    DROP TABLE #table_2
CREATE TABLE #table_2
(
code VARCHAR(2),
description VARCHAR(30)
)

INSERT INTO #table_2 VALUES
('1A','Finance'),
('2B','Law'),
('3C','Security'),
('4D','Marketing')

SELECT 
    company,
    description 
FROM #table_1 c
INNER JOIN #table_2 ct
    ON c.company_type_string like '%' + ct.code +  '%'
ORDER BY company

How to join a table by a column with tilde separated values

3 Answers3