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)
 not like in

Author  Topic 

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-04-22 : 05:14:46
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

52326 Posts

Posted - 2013-04-22 : 05:21:24
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 05:34:34
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

52326 Posts

Posted - 2013-04-22 : 05:47:12
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
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-04-22 : 06:50:05
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

52326 Posts

Posted - 2013-04-22 : 06:57:50
can you show your full query?

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-04-22 : 07:04:43
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 07:09:45
[code]
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)) +'%')
[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-04-22 : 07:15:08
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 09:14:21
welcome

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

- Advertisement -