CODEWITHSUNDEEP
sql-servert-sql
ChrisW
ChrisW

Reputation: 35

SQL How to compare if a variable is the same as a value in a database column?

The problem I have is the following:

I work with a ticket system that uses plugins to realize workflows. In this case I use SQL to presort incoming emails. The SQL query looks like this:

SELECT Count(Case when {MSG_CC_002_DeviceReg_MailBody} LIKE '%You have received an invoice from%' Then 1 END);

What I want to do now is instead of using LIKE and then a certain phrase like above, I want to compare this to a column in a database table that contains all necessary phrases.

The table has only two columns, phraseID and phrase. {MSG_CC_002_DeviceReg_MailBody} is the variable that needs to compared against the values of the column. So if the variable matches with an entry in the column it should just return 1.

[Edit:] This is just one of the things I want to use this for, I also have a variable {MSG_CC_002_DeviceReg_MailSender} that will provide the email address that I want to compare to a similar table that contains email addresses.

Is this possible? If so - how?

Upvotes: 1

Views: 3327

Answers (3)

Eric Brandt
Eric Brandt

Reputation: 8101

This will be dog-slow if you have a lot of phrases or email addresses, but it'll give you what you want.

SELECT COUNT(*) AS RetValue
FROM PhraseTable 
WHERE {MSG_CC_002_DeviceReg_MailBody} LIKE '%' + phrase + '%';

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can use join or a subquery:

select count(*)
from t
where exists (select 1
              from othertable ot
              where {MSG_CC_002_DeviceReg_MailBody} LIKE '%' + ot.phrase + '%' 
             ) ;

Upvotes: 2

Serdar
Serdar

Reputation: 797

Yes it is possible, you can achieve this with using dynamic query. Basically you need to construct your query as a string then execute it.

You can find examples and more information about dynamic query within the following link;

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Upvotes: 1

Related Questions