Doing LIKE on a subquery - 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 2005 Forums

Transact-SQL (2005)

Doing LIKE on a subquery

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2008-04-24 : 12:45:32

Hi

Im using sql server 2000 and I have sql statement that needs to do a LIKE statement from values from another table. An example would be the below

select Name, PostCode from Customers
where Post LIKE (select PartialPostCode + '%' from areas where area_arid = '123')

However if the above sub query returns more than one row then it will error. So I thought I would create a function to return a string such as the below and put it into vvariable

@strPostCodesLike = 'PostCode LIKE 'WS1 %' OR PostCode LIKE 'WS2 %'

And tried to execute the following SQL statement

select Name, PostCode from Customers WHERE @strPostCodesLike

However the above does not work, as I would need to use dynamic sql to get it to work. I cant use dynamic sql unfortunately.

Any help would be much appreciated

Many thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-24 : 12:57:30

[code]select C.Name, C.PostCode from
customers c JOIN areas a
on c.Post like a.PartialPostCode + '%'
where a.area_arid = '123'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2008-04-24 : 15:36:26

Thanks harsh_athalye

I totally forgot about using a join to do it

Thanks once again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources