Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a rather complex SQL query, that I actually received help from this SE site to come up with parts of. This query is designed to take a search term and search all relevant values in a database (this database stores all the relevant information for my partially CMS-ish site). It is a query that uses both JOINs and UNIONs. If I needed only to search one search term, it would be perfect, but alas, my boss knows that most people will expect a search bar to work more like what they might expect from Google (i.e., as it stands my query, so far, will treat whatever they type as the whole search term, as though it were wrapped in quotes, instead of separating each word).

While I know that the C# I use server-side will be needed to split there typed search term into different values (separating on white space), and stored in an array, this isn't really a problem, nor do I feel like any knowledge of C# is needed to answer this question. The real problem is that (given the query, shown below) I can't seem to wrap my head around how to compile a couple of the columns (formed by sql functions involving simple calculations) for use with more than one parameter. This is because I need ALL of the functionality of the searching of the database to be in one single (even if a bit oversized) query. I need this all to be in one single query because I also need to use both OFFSET and FETCH for pagination, and I can't see how I can implement that with multiple queries.

Okay, enough talk, here's the query I have so far (works perfect with only a single search term, but again, I need this to be dynamic enough to search on virtually any number of parameters):

SELECT NULL AS ObjectID, page AS location, 'pageSettings' AS type, page AS value, 'pageName' AS contentType, (len(page) - len(replace(UPPER(page), UPPER(@0), ''))) / len(@0) AS occurences, ((len(page) - len(replace(UPPER(page), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM PageSettings WHERE page LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT NULL AS ObjectID, pageTitle AS location, 'ExternalSecondaryPages' AS type, pageTitle AS value, 'pageName' AS contentType, (len(pageTitle) - len(replace(UPPER(pageTitle), UPPER(@0), ''))) / len(@0) AS occurences, ((len(pageTitle) - len(replace(UPPER(pageTitle), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ExternalSecondaryPages WHERE pageTitle LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT NULL AS ObjectID, eventTitle AS location, 'MainStreetEvents' AS type, eventTitle AS value, 'pageName' AS contentType, (len(eventTitle) - len(replace(UPPER(eventTitle), UPPER(@0), ''))) / len(@0) AS occurences, ((len(eventTitle) - len(replace(UPPER(eventTitle), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM MainStreetEvents WHERE eventTitle LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, value, 'content' AS contentType, (len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0) AS occurences, ((len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN TextObjects ON ContentObjects.ObjectID = TextObjects.ObjectID WHERE value LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, value, 'content' AS contentType, (len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0) AS occurences, ((len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN ParagraphObjects ON ContentObjects.ObjectID = ParagraphObjects.ObjectID WHERE value LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, value, 'content' AS contentType, (len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0) AS occurences, ((len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN TitleObjects ON ContentObjects.ObjectID = TitleObjects.ObjectID WHERE value LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, value, 'content' AS contentType, (len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0) AS occurences, ((len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN SubtitleObjects ON ContentObjects.ObjectID = SubtitleObjects.ObjectID WHERE value LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, linkText AS value, 'content' AS contentType, (len(linkText) - len(replace(UPPER(linkText), UPPER(@0), ''))) / len(@0) AS occurences, ((len(linkText) - len(replace(UPPER(linkText), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN LinkObjects ON ContentObjects.ObjectID = LinkObjects.ObjectID WHERE linkText LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, value, 'content' AS contentType, (len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0) AS occurences, ((len(value) - len(replace(UPPER(value), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN BulletLineObjects ON ContentObjects.ObjectID = BulletLineObjects.ObjectID WHERE value LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, caption AS value, 'content' AS contentType, (len(caption) - len(replace(UPPER(caption), UPPER(@0), ''))) / len(@0) AS occurences, ((len(caption) - len(replace(UPPER(caption), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN ImageObjects ON ContentObjects.ObjectID = ImageObjects.ObjectID WHERE caption LIKE '%' + CAST(@0 AS nvarchar) + '%' 
UNION 
SELECT ContentObjects.ObjectID, location, type, caption AS value, 'content' AS contentType, (len(caption) - len(replace(UPPER(caption), UPPER(@0), ''))) / len(@0) AS occurences, ((len(caption) - len(replace(UPPER(caption), UPPER(@0), ''))) / len(@0)) * 2 AS priority FROM ContentObjects JOIN ImageLinkObjects ON ContentObjects.ObjectID = ImageLinkObjects.ObjectID WHERE caption LIKE '%' + CAST(@0 AS nvarchar) + '%' 
ORDER BY contentType DESC, location ASC, type DESC

I guess the question in short, is how can I (or is it even possible to) still get the occurences column (and the priority column, though, If I get one, I get the other since they are based off of the same calculations) when I need to find all occurences of all of the arguments in their search text (that is, separate words in the typed search term)? I know I can use OR in the WHERE clause, but how can I use OR in an UPPER or REPLACE function?

ADDITIONAL INFORMATION, IF NEEDED OR CURIOUS ABOUT ANY OF THE ABOVE

There is probably a little bit of overhead using this query, but I really don't mind that a whole lot, honestly. I am way more worried about getting the functionality I need into a single query (which may not even be possible considering my needs, I guess).

Notice the (@0) which is just a placeholder used for parameterized queries (this is probably common knowledge, but just thought I would throw that out there just in case) where there search term is later thrown in by C# as an additional argument in the function that actually queries the database (again, probably obvious, but this is a common anti-SQL injection tactic, given my environment, if not many others).

Right now, the priority column simply always multiplies the occurences column by 2, but this is only until I work out exactly what each multiplier should be, considering the priority desired.

I am in a C#.net WebMatrix with web-pages environment. (Effectively this means I am using SQL Server CE).

I apologize if this question is a little confusing and am more than happy to clarify anything needed.

UPDATE

I am now more scared than ever that this may not be possible, as I have just discovered that using conditional logic (i.e., IF ... ELSE in my case) isn't supported by SQL SERVER CE. (hangs head in dissapointment...)

UPDATE AGAIN

It seems that, considering the limitations to SQL Server CE and my own requirements, this isn't entirely possible. SQL Server CE is still powerful, but just not as powerful as its bigger, more complete, brother. I believe, though, for my own environment, I may indeed have found a way to complete what's left of my task using my server-side language to manipulate the compilation of the SQL query string. As long as I end up with one query in the end, I can still use OFFSET and FETCH for pagination in the way I was taught. I just want to thank any who looked this over and tried to help. Now, come on, C#, we have some work to do...

share|improve this question
    
In your other question, you were given a clever answer that works great for the exact scenario you presented... but it doesn't work on word boundaries, nor does it scale particularly well. You could be in for a lot of pain, as fulltext indexing is not supported on CE. –  Jon Seigel Jul 8 '13 at 16:48
    
@JonSeigel It seems my continued research would agree with you, Sir. And yes, in my other question, I did not take this into account, nor did I know I would need to. However, I have found some clever C# code that at least allows me to split the entries (including strings wrapped in quotations as single search terms) into a C# list. From there I can manipulate the compilation of the string through C# conditional logic. As long as I end up with a single query in the end, I can still make this work (even with the CE limitation). –  VoidKing Jul 8 '13 at 21:42
    
@JonSeigel SQL is so powerful, whenever I ask if something can be done in it, the answer is almost always (if not always) "yes." The same does not hold true for SQL CE, unfortunately, but I still love WebMatrix for being such a great introductory IDE for beginning programmers, if nothing else. I have learned SO much because I wasn't overwhelmed with too many technical issues all at once (as it probably would have been if I had just started in regular IIS). Thank you for looking at this, and I suppose then, the real answer to this question is: "not possible considering the limitations." –  VoidKing Jul 8 '13 at 21:46
    
Yeah, fulltext indexing includes a comprehensive set of word breakers (i.e., word boundaries) that are language specific. It's really unfortunate you can't use that, because it's exactly what you need. Basically you're in for either a kinda flaky, inefficient solution, or reimplementing a skeleton version of fulltext indexing. –  Jon Seigel Jul 8 '13 at 21:50
    
@JonSeigel Well, although I haven't yet written it, in my head it seems that (since I know I can separate their search string into separate search terms [quotes considered]) I should be able to use C# loops and enough conditional logic to make this work, and I think it shouldn't be flaky. Inefficient, yeah probably on speed, but what I am doing here, isn't that time sensitive, so I don't think (if my assumptions so far are correct) that any user would notice. –  VoidKing Jul 8 '13 at 21:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.