SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 not like in
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 04/22/2013 :  05:14:46  Show Profile  Reply with Quote
Hi,
How can I place the like inside this sql:
where
ltrim(rtrim(tbl2.Name)) NOT IN (select ltrim(rtrim(Name)) from tblMain group by Name)

to become:
where
ltrim(rtrim(tbl2.Name)) NOT LIKE IN (select ltrim(rtrim(Name)) from tblMain group by Name)

?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  05:21:24  Show Profile  Reply with Quote
should convert to a join

..
tbl2
JOIN (SELECT  ltrim(rtrim(Name)) AS Name from tblMain group by Name) m
ON ltrim(rtrim(tbl2.Name)) NOT LIKE '%' + m.Name + '%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/22/2013 :  05:34:34  Show Profile  Reply with Quote
That should be...

LEFT JOIN (SELECT ltrim(rtrim(Name)) AS Name from tblMain group by Name) m
ON ltrim(rtrim(tbl2.Name)) LIKE '%' + m.Name + '%'
WHERE m.Name IS NULL



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  05:47:12  Show Profile  Reply with Quote
in that case this is better

where NOT EXISTS (SELECT 1 FROM tblMain WHERE ltrim(rtrim(Name)) LIKE '%' + ltrim(rtrim(tbl2.Name)) + '%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 04/22/2013 :  06:50:05  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

should convert to a join

..
tbl2
JOIN (SELECT  ltrim(rtrim(Name)) AS Name from tblMain group by Name) m
ON ltrim(rtrim(tbl2.Name)) NOT LIKE '%' + m.Name + '%'

I get this error now:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  06:57:50  Show Profile  Reply with Quote
can you show your full query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 04/22/2013 :  07:04:43  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

can you show your full query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



select
CustomerName, CustomerShortName, Residency, Residency, Residency, City, 0, 0,
isnull(ct.TypeID, 1),
0, 0, 0, 0, 1,
2
from
tblSSIS as ssis
left join CT as ct on ssis.CT = ct.Description
where
ltrim(rtrim(CustomerName)) not like '%' + (select ltrim(rtrim(CustomerName)) from Customers group by CN)

Edited by - arkiboys on 04/22/2013 07:39:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  07:09:45  Show Profile  Reply with Quote

select
CustomerName, CustomerShortName, Residency, Residency, Residency, City, 0, 0, 
isnull(ct.TypeID, 1), 
0, 0, 0, 0, 1, 
2
from
tblSSISImportCustomers as ssis
left join ClientTypes as ct on ssis.ClientType = ct.Description
where
--ltrim(rtrim(CustomerName)) not in (select ltrim(rtrim(CustomerName)) from Customers group by CustomerName) 
 not exists (select 1 from Customers where ltrim(rtrim(ssis.CustomerName) like '%' + ltrim(rtrim(CustomerName)) +'%')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 04/22/2013 :  07:15:08  Show Profile  Reply with Quote
Thank you

Edited by - arkiboys on 04/22/2013 07:16:21
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/22/2013 :  09:14:21  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000