By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
"ColumnA" VARCHAR(8),
"ColumnB" VARCHAR(16)
);
INSERT INTO YourTable
("ColumnA", "ColumnB")
VALUES
('1.5 FLOZ', '1.5 FLUID OUNCES'),
('CBOY', 'CellBoy');
2 rows affected
SELECT *,
CommonLetters = (
SELECT COUNT(DISTINCT a.letter)
FROM GENERATE_SERIES(1, LEN(t.ColumnA)) NumA
CROSS APPLY (
SELECT
SUBSTRING(t.ColumnA, NumA.value, 1)
) a(letter)
JOIN GENERATE_SERIES(1, LEN(t.ColumnB)) NumB
ON SUBSTRING(t.ColumnB, NumB.value, 1) = a.letter
)
FROM YourTable t
| ColumnA | ColumnB | CommonLetters |
|---|---|---|
| 1.5 FLOZ | 1.5 FLUID OUNCES | 7 |
| CBOY | CellBoy | 4 |
SELECT *,
CommonLetters = (
SELECT COUNT(DISTINCT a.letter)
FROM GENERATE_SERIES(1, LEN(t.ColumnA)) NumA
CROSS APPLY (
SELECT
SUBSTRING(t.ColumnA, NumA.value, 1)
) a(letter)
WHERE CHARINDEX(a.letter, t.ColumnB) > 0
)
FROM YourTable t
| ColumnA | ColumnB | CommonLetters |
|---|---|---|
| 1.5 FLOZ | 1.5 FLUID OUNCES | 7 |
| CBOY | CellBoy | 4 |