T-sql – How to get only the strings before a character in SQL Server


I am trying to write a sentence that gets only the strings which are just before a certain character.
for example:

SET @TEST =  'CAMP1 = 4 AND CAMP2 = 5 OR CAMP3 = 7 AND CAMP4 = 9'

I am trying to to parse the string @TEST and get only the camps one by one and check if this camps (CAMP1, CAMP2, CAMP3, CAMP4) realy exist.

I don't know if loop would be necessary to sweep the string and get the strings before '=' and after the logic operators 'AND, OR'.

Any help pls ?

Best Answer

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 [ ] or " ", 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!):

  1. Set a "some not found" flag to 0
  2. Copy @Test into @LeftToProcess
  3. While there is at least one "=" character in @LeftToProcess (check with charindex) do the following:
    1. Copy everything before the first "=" into @ColumnName
    2. Take everything after the first "=" and put it back into @LeftToProcess
    3. TRIM() @ColumnName
    4. Replace AND with `` in @ColumnName
    5. Replace 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 AND/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)
    6. Check using sys.objects joined to sys.columns that your table or view contains a column of the name now in @ColumnName
    7. If not, set @SomeNotFound to 1
    8. (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)
  4. If @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.