SQL - Finding substring matches within the same column-CodePudding

I have a column with names (string) of parts and subparts, and I would like to get a two column table with the relations between parts and subparts. The relation exists if the name is a substring of another name.

For example, this is what I have:

Name
AAA
BBB
CCC
AAA_1
AAA_2
BBB_1
CCC_3

And I would like to get this:

Part	Subpart
AAA	AAA_1
AAA	AAA_2
BBB	BBB_1
CCC	CCC_3

CodePudding user response：

Try Following Query, its give exact output, i try this using like clause and self join

CREATE TABLE SubStrTable 
(
  Name  varchar(20)
);

INSERT INTO SubStrTable (Name) VALUES ('AAA');
INSERT INTO SubStrTable (Name) VALUES ('BBB');
INSERT INTO SubStrTable (Name) VALUES ('CCC');
INSERT INTO SubStrTable (Name) VALUES ('AAA_1');
INSERT INTO SubStrTable (Name) VALUES ('AAA_2');
INSERT INTO SubStrTable (Name) VALUES ('BBB_1');
INSERT INTO SubStrTable (Name) VALUES ('CCC_3');



Select T1.Name As Part,T2.Name As Subpart From SubStrTable T1
Left Join SubStrTable T2
ON T2.Name Like  T1.Name   '_[0-9]'
Where T1.Name Not Like    '%_[0-9]'

CodePudding user response：

We can try a self join here:

SELECT t1.Name AS Part, t2.Name AS Subpart
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.Name LIKE t1.Name   '[_]%'
WHERE t1.Name NOT LIKE '%[_]%'
ORDER BY t1.Name, t2.Name;