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 |