SQL: Specific LIKE statement

[Toni]

If I need to load all records with name John, than I can use "Where
Name='John'";
If I need to load all records which contains pattern Jo, than I can use
"Where Name like '%Jo%'";

But I need to load all records where field Name is one of the names in
this string: "John is director, Jack is programmer, Jim is Analyst, James is
manager".

So I need to load all fields from table Names where field Name is John,
Jack, Jim and James.

Ho to do that with SQL?

[Mark Rae [MVP]]

"Toni" <to****@hotmail.comwrote in message
news:gd**********@sunce.iskon.hr...

If I need to load all records with name John, than I can use "Where
Name='John'";
If I need to load all records which contains pattern Jo, than I can use
"Where Name like '%Jo%'";

But I need to load all records where field Name is one of the names in
this string: "John is director, Jack is programmer, Jim is Analyst, James
is manager".

So I need to load all fields from table Names where field Name is John,
Jack, Jim and James.

How to do that with SQL?

Firstly, since you've posted in the ASP.NET newsgroup as opposed to one of
the SQL Server newsgroups, I'm assuming that you need to do this in one of
the ASP.NET languages rather than T-SQL...

string strWhereClause = String.Empty;
string strRaw = "John is director, Jack is programmer, Jim is Analyst, James
is manager";
string[] strSplit = strRaw.Split(',');
foreach (string strName in strSplit)
{
strWhereClause += "'" + strName.Trim().Split(' ')[0].Trim() + "',";
}
strWhereClause = "WHERE [Name] IN(" + strWhereClause.TrimEnd(',') + ")";

N.B. the above isn't particularly efficient or robust, but it should be
enough to get you started...

I'm sure that there are "cleverer" methods using RegEx etc...

Secondly, you should avoid using any of the SQL Server reserved words (e.g.
Name) for your own objects...

Finally, it's best to avoid dynamic SQL in ASP.NET apps because of the risk
of SQL injection...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[bruce barker]

there are several options.

use the "or". where name like 'jo%' or name like 'jack%'
load the name into a temp table or table variable and join. if you don;t
need wild cards use
where name in (select name from @tbl)
if you do then just use a join

join @tbl on mytable.name like @tbl.name
-- bruce (sqlwork.com)


Toni wrote:

If I need to load all records with name John, than I can use "Where
Name='John'";
If I need to load all records which contains pattern Jo, than I can use
"Where Name like '%Jo%'";

But I need to load all records where field Name is one of the names in
this string: "John is director, Jack is programmer, Jim is Analyst, James is
manager".

So I need to load all fields from table Names where field Name is John,
Jack, Jim and James.

Ho to do that with SQL?