What is the T-SQL syntax to extract a specific word from a column of Table A based on the value of a column in Table B?

Question

I am using SQL Server 2014 and I have the following column (called Details) in Table A:

Details
STD RN
CPL DEL REV
DLX RN
FAS REV
CJS SUP REV
COS RN

I have a column (called Category) in Table B:

Category
STD
CPL DEL
DLX
FAS
CJS SUP
COS

I need to run a query against Table A so that my output is as follows:

Details          Category

STD RN             STD
CPL DEL REV        CPL DEL
DLX RN             DLX
FAS REV            FAS
CJS SUP REV        CJS SUP
COS RN             COS

There is no link between Table A and Table B. Is there a way to write a query that will look up the words in the 'Details' of Table A and if it matches the word in the 'Category' column of Table B, then it will output the values of Table B?

Answer 1

If the order of these words is always the same (no DEL CPL or CPL REV DEL) this should be easy:

DECLARE @Details TABLE(Detail VARCHAR(100));
INSERT INTO @Details VALUES
 ('STD RN')
,('CPL DEL REV')
,('DLX RN')
,('FAS REV')
,('CJS SUP REV')
,('COS RN');


DECLARE @Categories TABLE(Category VARCHAR(100));
INSERT INTO @Categories VALUES
 ('STD')
,('CPL DEL')
,('DLX')
,('FAS')
,('CJS SUP')
,('COS');

SELECT d.Detail
      ,c.Category
FROM @Details AS d
INNER JOIN @Categories AS c ON d.Detail LIKE c.Category + '%'

This will combine all Details with (all) category(ies), that have the same beginning.

The result

Detail  Category
STD RN  STD
CPL DEL REV CPL DEL
DLX RN  DLX
FAS REV FAS
CJS SUP REV CJS SUP
COS RN  COS

But to be honest: What you really should do is change the design!