May 25, 2006 at 3:12 am
Found this: http://www.developmentnow.com/g/113_2006_5_0_0_759056/Getting-T-SQL-to-use-LIKE-REPLACE-and-IN--all-at-the-same-time.htm on a site which doesn't seem to allow anyone to post. Anyone got any ideas?
Re: Getting T-SQL to use LIKE, REPLACE, and IN... all at the same time???????????????????
Hugo Kornelis 5/21/2006 12:00:00 AM
On Sat, 20 May 2006 20:33:33 -0400, "A_Michigan_User" wrote:
How in the world would you do this?
The user enters a word like: "BROWN".
The database contains a VarChar() field like: "RED,GREE*,BR*N,BLU*
A match is found because the 3rd word "BR*N" matches "BROWN".
I guess I would have to use some combination of:
LIKE (for the wildcards)....
REPLACE (to replace the user-friendly *, with the T-SQL % character)....
IN (to match a list of values like RED,GREEN,BROWN,BLUE, etc)
Also... the wildcard characters are in the database... NOT passed from
the user.
But I can't seem to get them all working at the same time.
Help!
(I'm working with MS SQL 2000, asp.net, vb.net... and wanted to use a
parameterized query.)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 25, 2006 at 3:14 am
[And this is the first reply copied from the original site:]
Hi A_Michigan_User,
Erland has some techniques on his site to convert a comma-seperated
string to a table with one value per row. See this article:
http://www.sommarskog.se/arrays-in-sql.html
With that table, the rest is a lot easier to accomplish:
SELECT ??? -- Enumerate columns here
FROM TableWithSplitStrings AS t
-- JOIN other tables ???
WHERE 'Brown' LIKE REPLACE(t.TheColumn, '*', '%')
(Untested - if you prefer a tested reply, see http://www.aspfaq.com/5006)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply