Prev: SQL 2005\2008 Hashbytes - salt key
Next: Particular query problem
From: AA2e72E on 23 Apr 2010 07:21
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 23 Apr 2010 08:02
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 23 Apr 2010 08:47
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 23 Apr 2010 17:35
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;

 | 
Pages: 1
Prev: SQL 2005\2008 Hashbytes - salt key
Next: Particular query problem