asked on

Modifying this query so it searches based on search text?

I have two unrelated tables:
published_ads (with ID, title, email_address)
email_addresses (with ID, email_address)

I have a stored procedure with one parameter (@searchText) and this parameter is used for the following (oversimplified) query:

SELECT * FROM published_ads WHERE [title] like @searchText

This is what I would like to do:
if the string in @searchText is a record of table [email_addresses], then the query will search in [published_ads.email_address]. Otherwise, the query will search [published_ads.title].

In other words, let's say @searchText is 'myEmail@email.com' and this string is in table [email_addresses]. Then the query will look like:
SELECT * FROM published_ads WHERE [email_address] like '%myEmail@email.com%'

On the other hand, @searchText is 'shoes', which is not in table [email_addresses]. Then the query will look like:
SELECT * FROM published_ads WHERE [title] like '%shoes%'

Is this possible?

Thanks.

knightEknight

SELECT * FROM published_ads
WHERE [email_address] like @searchText
OR title like @searchText

MyersA

ASKER

Well, I had that coming.

The problem there is that if the title has the email address as text, the the record will be displayed even if the email address isn't actually that string.

So if someone adds a record whose [published_ads.title] is 'sales@walmart.com' and its [published_ads.email_address] is 'I_Got_You@sucker.com' then this record will be displayed even though the record is clearly not from 'sales@walmart.com'.

That's why I don't want to use that solution.

mmr159

I suppose taking the cheap way with dynamic SQL is out of the question?

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmr159

If you go with wdosanjos method

> if @searchText LIKE '%@%'

use this instead

if CHARINDEX('@',@searchText) > 0

or PATINDEX

They provide better performance... probably negligible in this case, but why not.

jvejskrab

IF EXISTS (SELECT 1 FROM email_addresses WHERE email_address = '%'+@searchText+'%')
SELECT * FROM published_ads WHERE [email_address] like '%'+@searchText+'%'
ELSE
SELECT * FROM published_ads WHERE title like '%'+@searchText+'%'

jvejskrab

You can also have it in one query....

SELECT *
FROM published_ads
WHERE CASE
WHEN CHARINDEX('@',@searchText) > 0 THEN [email_address]
ELSE [title]
END like '%'+@searchText+'%'

or if an occurence of '@' char isn't enough, you can write some email validation function and use it within the query. Someting like this

SELECT *
FROM published_ads
WHERE CASE
WHEN dbo.IsValidEmail(@searchText) = 1 THEN [email_address]
ELSE [title]
END like '%'+@searchText+'%'

MyersA

ASKER

I ended up using a combination these two solutions.

Thanks again for all your help.

jvejskrab

MyersA: Can you send how the final query looks like? Thanks