Count how many times a word exist in column

I have a table 1:

CREATE TABLE table1
INSERT INTO table1 values('XYZ')
INSERT INTO table1 values('ABC')
INSERT INTO table1 values('XYZ~ABC~AAA')
INSERT INTO table1 values('123')

Then, I have string 'ABC~XYZ~123'. I need to split this string into each word by using SQL:

Select VALUE FROM STRING_SPLIT('ABC~XYZ~123','~')

The return is table2

ABC
XYZ
123

I want to count how many times each word in table2 existed in table 1

The expected output is

ABC|3
XYZ|2
123|1

Any ideas on this?

If I understand your case correctly, the next statement may help:

Text and table:

DECLARE @text varchar(100) = 'ABC~XYZ~123'
CREATE TABLE Data (
   Id int,
   [Text] varchar(100)
)
INSERT INTO Data
   (Id, [Text])
VALUES
   (1, 'XYZ'),
   (2, 'ABC'),
   (3, 'XYZ~ABC~AAA'),
   (4, '123~ABC')

Statement:

SELECT t.[value] AS [Word], j.[Count]
FROM STRING_SPLIT(@text, '~') t
LEFT JOIN (
   SELECT s.[value], COUNT(*) AS [Count]
   FROM Data d
   CROSS APPLY STRING_SPLIT(d.[Text], '~') s
   GROUP BY s.[value]
) j ON t.[value] = j.[value]

Result:

-----------
Word  Count
-----------
ABC   3
XYZ   2
123   1