SQL Wildcard using %

[nerdalert1]

Hello all how do I do this.

- I have 2 fields 1 called ScanName and 1 called ScanOrderName

- I have 2 values that can be vice versa in these fields like so:

Value 1: B Barker
Value 2: Bob Barker

I want to somehow wild card search and flag a partial match. In this case it would be a wildcard match. So it could be like so either:

ScanName = B Barker
ScanOrderName = Bob Barker

ScanName = Bob Barker
ScanOrderName = B Barker

In this case I want it flagged as match. So its a whole word match I guess you can say.

However in this case I do not want a match

ScanName = Charlie
ScanOrderName = Charl

or

ScanName = Charl
ScanOrderName = Charlie

So a whole word has to match.
Thanks all

 

[donutman]

One way is to use this FAQ: Passing a list of values to a Stored Procedure (Part II) faq183-5207. You would have to do a cross join between the two tables that are generated from parsing ScanOrderName and ScanName (using a space as the delimiter). If there is a match (using an inner join) then the rowcount would be >0 and you could use that in the Where clause.
If however there will always be at most one space, then there is another alternative, but I don't want to work on that solution unless you need it. Let me know.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[nerdalert1]

Might be more than one space possibly.

 

[donutman]

I didn't mean contiguous spaces, but the possibility that there would be 3 words. Take a stab at the use of ESquared's Split function to parse the two fields and I'll help you with any errors you might get.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]