troubleshooting Question

Complex Sql query

Avatar of onebite2
onebite2 asked on
DatabasesMicrosoft SQL Server 2005
1 Comment1 Solution204 ViewsLast Modified:
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.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros