Alright, so the following has stumpted me and a few others in the office, and we just can't figure it out.
I have develope a program that allows us to go out to a specific database and search one of the fields based on specified values. Now that seems all simple and everything, except this field, its XML. We are searching for values within the XML (not the whole thing).
To do this I am using a SQL statement like so
cast(TxOriginal as varchar(max)) LIKE '%name="Branch" value=" 25"%'
Now this works fine except for one small problem. Notice the leading whitespaces within the value field? Those are required for us to retrieve values from the field. The problem with that is when users use my program, they have to make sure they have the correct number of leading white spaces for a match to be found.
And here's the stumper. I have been unable to find a way around this. I have tried inserting
%[ ]% after
value=" but the problem with this is spaces aren't the only thing being wildcarded, it will wildcard other characters. I have also tried using one of the percent signs on either side, and while I have had some success, if a value does NOT have leading white space, then once again, we have an issue (the record isn't fetched despite the fact that '%' is suppose to be zero or multiple).
I can't use RegEx as it must work on SQL Server 2008 (and possible 2005), CLR is also not an option as this needs to be lightweight.
Any SQL gurus out there able to help me out?