Select not superstring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select not superstring

Author

Topic

Boxersoft
Starting Member

28 Posts

Posted - 2011-04-17 : 11:42:56

Not quite sure how best to describe this problem. I have a set with string values, some of which will be substrings of the values in other rows. I want to deselect any rows that start with values held in other rows.

For example, given the following data:

DECLARE @data TABLE (
name varchar(10),
value varchar(250)
)

INSERT @data
SELECT 'One', 'This is a string' UNION ALL
SELECT 'Two', 'This is a string too' UNION ALL
SELECT 'Three', 'This is a string - again' UNION ALL
SELECT 'Four', 'This is another string' UNION ALL
SELECT 'Five', 'Different string'

... the value columns in rows Two and Three are 'superstrings' of that in row One, so they are not wanted. I want to select rows One, Four and Five. I'm sure it must be possible but the solution is eluding me. How can I do it?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-17 : 13:25:44

Does this work?

DECLARE @data TABLE (
name varchar(10),
value varchar(250)
)

INSERT @data
SELECT 'One', 'This is a string' UNION ALL
SELECT 'Two', 'This is a string too' UNION ALL
SELECT 'Three', 'This is a string - again' UNION ALL
SELECT 'Four', 'This is another string' UNION ALL
SELECT 'Five', 'Different string' 

SELECT * FROM @data AS d WHERE NOT EXISTS (SELECT 1 FROM @data d2 WHERE d.value LIKE d2.value + '%' AND d.name != d2.name)

--
Gail Shaw
SQL Server MVP

Boxersoft
Starting Member

28 Posts

Posted - 2011-04-18 : 06:55:55

<groan> I had tried similar approaches several times, but using variations of the IN subquery clause. Nothing seemed to work for me. Your approach works just fine - thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources