Complex Sql query

Posted on 2007-07-26
Medium Priority
Last Modified: 2010-03-19
I have a interesting scenario here, not able to figure out how to fix this issue.

 if exists(select MasterID from ReqFormula where charindex(Ltrim(rTrim(str(@MasterID))),Formula) > 0)

  Print 'Error Master course used in ReqFormula'
      Return 2
The above statement doesn't work because if @MasterID = 29 and if there is {#329} in 'Formula' field then Charindex returns > 0 which is incorrect because i'm looking for 29 not 329.

This is what my table(ReqFormula) looks like
MasterID   Formula                                                                    FormulaText
1               {(}{#70}{AND}{#74}{)}{OR}{#14}                             (DAA115 AND DAA230) OR CV131
2               {(}{#63}{AND}{#69}{)}{OR}{(}{#7}{AND}{#13}{)}    (DAA100 AND DAA110) OR (CV101)
5               {#7}{AND}{#253}                                                     CV101 AND HU120
6               {#329}{AND}{#253}                                                 MBT101 AND HU120

In the above table under formula column each number is a MasterID. Look at MasterID 6(Last column), this records gets picked up because under Formula column there is 329 even thought our variable @MasterID is actually looking for 29.

I think my above query is correct but i need to filter it little more so that @masterID matches with number in the formula column. I would like see if i can separate each numeric value using the course separator "AND" or  "OR" and then do a string comparison individually so that my @masterID matches with that exact number in the formula column/field.

Let me know if there is a way to do this.

Question by:onebite2
1 Comment
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19575990
so you are actually looking for {#29} ?

then, make it as easy as this:
 if exists(select MasterID from ReqFormula where Formula like '%{#' + cast(@MasterID as varchar(200)) +'}%' )

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question