Ignore white spaces within a Like statement

[JOSheaIV]

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?
Thanks

[diafol]

I'm not well versed in SQL Server, but MySQL allows this:

SELECT * FROM table WHERE field REGEXP 'name="Branch" value="[ ]+25"'

Which works. Are you sure that you can't use regex?

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx#S1

[JOSheaIV]

I thought u had to use CLR for the Regex, but now I am seeing mixed results.

[diafol]

If the number of spaces is constant (e.g. 10), how about using something like REPLICATE(' ', 10)?

Are your users going to be intereacting directly with SQL - i.e. writing the statement, or is it put together in code e.g. vb.net or similar?

[JOSheaIV]

No sadly the spacing can vary, and using a replace is out as it will remove the spacing between the "name" and "value" attributes

It's being written in C# (.NET 3.5), where I create the query string in the code and then query the database with it.

Also, I don't remember if I mentioned this, but pulling the whole database and parsing it in the C# code should be considered a last resort, there are no other possible way scenarios, as the table that's queries, I have seen those range any where from say 10 records, to over 4 million (I am not lying about that #)

[diafol]

Ok how about splitting the string...

...name="Branch" value="| 25"...

So search for 'name="Branch" value="' - get the position.

I know next to no T-SQL, so bear with me. Something like CHARINDEX() to find the position and add the length of the string to that to get the start position for the next check. You can use SUBSTRING() to clip the text to start with ' 25"...'.

LTRIM that to get to '25"...'

CHARINDEX() that on '"' and SUBSTRING again to get the value '25'

That sounds longwinded, but it should work.

So your SQL wuld be something like...

SELECT ..., WHERE @name = 'Branch' AND @value=25

[JOSheaIV]

Hmm that might be something worth trying. I'll have to try implementing something like this next time I have some free time at the office

[JOSheaIV]

So sorry I haven't replied sooner, but I finally got some time at the office to get back to this.

I ended up finding a solution to this, CROSS APPLY. I used cross apply, as well as SQL Server's version of Xpath, which actually allows me to read the XML within the column, and match up the values.

It did get a little hairy, and the SQL isn't the prettiest, but it does the trick.

[diafol]

Fantastic Angel. Would you consider pasting your solution here, so that others may benefit from all your blood, sweat and tears?