How to get unique value from sql table based on charachter length of value

Question

Here I have two columns like below example column1 & column2 in sql table and i want to get unique value row on the basis of column2 column value from table
Below example of dummy table

 Column1             Column2

----------         -------------

 1001                   ab
 1001                   abc
 1001                   abcd
 2001                   wxyz
 2001                   wxy
 2001                   wx

In above example value starting from a & another value starting from w in Column2
On the basis of same value character length, i want to get result like below
Output:

 Column1                Column2

----------           -----------

1001                     abcd
2001                     wxyz

Thanks in advance of help :)

Answer 1

If you are looking if your values in column2 are somewhere included in other rows, in other words: If you are looking for rows with combinations of characters which are unique on their own, this might be your solution:

CREATE TABLE TestTable(Column1 INT,Column2 VARCHAR(100));
INSERT INTO TestTable VALUES
 (1001,'ab')
,(1001,'abc')
,(1001,'abcd')
,(2001,'wxyz')
,(2001,'xyz')
,(2001,'yz');

SELECT *
FROM TestTable
WHERE NOT EXISTS(SELECT 1 
                 FROM TestTable AS x 
                 WHERE x.Column1=TestTable.Column1 
                   AND LEN(x.Column2)>LEN(TestTable.Column2)
                   AND x.Column2 LIKE  '%' + TestTable.Column2  + '%'
                )

DROP TABLE TestTable;