Complex Sql query

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.

Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)) +'}%' )
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.