CODEWITHSUNDEEP
sqlsql-server
Wilson Parson
Wilson Parson

Reputation: 29

SQL Server select all records that do not have duplicate value with prefix

Given the following table:

Url
--------------
Id Url
1  foo.com
2  www.foo.com
3  bar.com
4  baz.com
5  www.baz.com

How would I go about selecting only the URLs that do not have a corresponding record with a 'www.' prefix? (In this example, the desired result would be to return only bar.com.)

Upvotes: 2

Views: 73

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use not exists:

select t.*
from t
where t.url not like 'www.%' and
      not exists (select 1 from t t2 where 'www.' + t.url = t2.url);

Here is a SQL Fiddle.

Upvotes: 1

Igor Cova
Igor Cova

Reputation: 3514

You also can do it like there

select t.*
from as t
outer apply (
  select 1 as IsExistWithWWW
  from t as t2 
  where lower(t2.url) = lower(replace(t.url, 'www.', ''))
) as a
where lower(t.url) not like 'www.%'
  and a.IsExistWithWWW is null

Use lower for make script stronger

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

you could use replace and group by for filter the single results 

select  replace( Url 'www.', '')  
from my_table 
group by  replace( Url 'www.', '')
having count(*) = 1

Upvotes: 1

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Another way to achieve it using CHARINDEX:

select url from (
    select SUBSTRING(url, CHARINDEX('www', url) * 5, len(url)+1) url  
    from Table1
) as T group by url
having count(url)=1

SQL HERE

Upvotes: 0

Related Questions