This looks like you are accepting a filtering clause that is going to be used as part of an ad-hoc query, and you want to try verify that the query won't produce a "column not found" error. This is almost certainly something you don't really want to do so I would question the design overall...
This sort of string parsing is not going to be very efficient or pretty in TSQL so would be probably better done in another layer, and as your business logic is no doubt putting the string together anyway it would be better to check as that happens and discover any error earlier in the process. If your business logic isn't making the string (i.e. you are accepting it from 3rd party code, or worse, a user) then you will have to contend with a lot of variations in the string (tabs instead of spaces, no spaces around the "=", extra whitespace including line breaks, names escaped with
", and so on).
But assuming you have no choice at all because this is someone else's design and you have no power to change it, you could try something like (pseudo code here, not feeling unlazy enough right now write detailed TSQL string manipulation!):
- Set a "some not found" flag to 0
- Copy @Test into
- While there is at least one "=" character in
@LeftToProcess (check with
charindex) do the following:
- Copy everything before the first "=" into
- Take everything after the first "=" and put it back into
AND with `` in
OR with `` in
@ColumnName (these two steps take out the logical operator, not that they make a massive set of assumptions like "there will always be exactly one space character between
OR and the name, if the input is coming from a user or third party you'll have to be a lot more clever than this)
- Check using
sys.objects joined to
sys.columns that your table or view contains a column of the name now in
- If not, set
@SomeNotFound to 1
- (end of
WHILE loop, as we stripped off the part of the string we've just checked when we go back to step 3 the next "=" will be found or if there isn't any more we go to step 12)
@SomeNotFound is still 0 at the end of the loop, everything mentioned exists in the target object
I hasten to make clear, in case my tone above does not, that I strongly advise against this sort of thing - it is hard to get absolutely correct for all possible inputs and the result will be difficult to maintain if it need to be expanded to deal with other sorts of clause.
There is unlikely to be any significantly cleaner or easier way to do exactly what you asking for in TSQL, your overall design needs a rethink to avoid it I feel.