From: AA2e72E on
Given:

select cde from mytable;

A1%
BV7%
CV5%

What SQL will give me the following as a result (SQL snippet):

where cde like 'A1%' or cde like 'BV7%' or cde like 'CV5%'

Thanks.
From: Erland Sommarskog on
AA2e72E (AA2e72E(a)discussions.microsoft.com) writes:
> Given:
>
> select cde from mytable;
>
> A1%
> BV7%
> CV5%
>
> What SQL will give me the following as a result (SQL snippet):
>
> where cde like 'A1%' or cde like 'BV7%' or cde like 'CV5%'

It is far from clear what you are asking for, but if I make a blind guess
that your table contains search patters that you want to build a query
string from, this could be a solution:

SELECT 'WHERE ' + convert(nvarchar(MAX),
(SELECT 'cde LIKE ' + cde AS [text()]
FROM mytable
FOR XML PATH(''), TYPE)

The solution requires SQL 2005 or later.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Gert-Jan Strik on
Here is another solution. You should be able to run the result of this
batch as SQL query just by copying and pasting.

set nocount on
create table #t(sp varchar(20))
insert into #t values ('A1%')
insert into #t values ('BV7%')
insert into #t values ('CV5%')

print 'SELECT * FROM my_table WHERE'
select 'cde LIKE '''+sp+''' or ' from #t
print '1=0'

drop table #t
set nocount off

--
Gert-Jan


AA2e72E wrote:
>
> Given:
>
> select cde from mytable;
>
> A1%
> BV7%
> CV5%
>
> What SQL will give me the following as a result (SQL snippet):
>
> where cde like 'A1%' or cde like 'BV7%' or cde like 'CV5%'
>
> Thanks.
From: --CELKO-- on
CREATE TABLE Patterns
(pattern VARCHAR (15) NOT NULL PRIMARY KEY);
INSERT INTO Patterns
VALUES ('A1%'), ('BV7%'), ('CV5%');

SELECT * -- never use * in production code
FROM MyTable AS T, Patterns AS P
WHERE T.cde LIKE P.pattern;