Here is what we can do with IN:

SELECT * FROM language WHERE langkey IN ( SELECT acct_desc || '-' FROM
dbo.product WHERE acct_code LIKE 'WSDOM%');

but what if you dont want exact matching like IN gives and want to do
a LIKE... what can you do.. Here is what I want:

SELECT * FROM language WHERE langkey INLIKE ( SELECT acct_desc || '-%'
FROM dbo.product WHERE acct_code LIKE 'WSDOM%');

On 2010-08-17 17:30, The Quiet Center wrote:
For future questions, you are likely to get more help if you provide
table definitions and some sample data.

create table language (
langkey varchar(20) not null primary key
);

create table product (
acct_code varchar(20) not null primary key,
acct_desc varchar(20) not null
);

insert into product (acct_code, acct_desc)
values ('WSDOM1', 'A'), ('WSDOM21', 'B');

insert into language (langkey)
values ('ALFA'), ('BETA'), ('GAMMA');

select l.* from language l
join product p
on substr(l.langkey,1,length(p.acct_desc)) = p.acct_desc
where p.acct_code like 'WSDOM%';

LANGKEY
--------------------
ALFA
BETA

The substr function may have a different syntax in your dbms, but there
should be something similar that can be used.

You might want to look into your table design to see if it can be improved.


HTH
/Lennart

On Aug 17, 12:59*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

acknowledged

wow, thanks.

CREATE TABLE Patterns (search_pattern VARCHAR(25) NOT NULL);
INSERT INTO Patterns VALUES ('AB%'), (BC%'), etc.


SELECT S.some_column, P.pattern, ..
FROM Source AS S, Patterns AS P
WHERE S.some_column LIKE P.pattern;

You can also use a table constructor directly in the query.

(SELECT pattern
FROM (VALUES ('AB%'), (BC%'), etc.) AS X(pattern))
AS P

--CELKO-- wrote on 18.08.2010 18:59:
Nice trick

Although it won't help the OP because he/she is using SQL Server (as far as I can tell) and last time I checked, SQL Server did not support the row/table constructor using the VALUES clause

Regards
Thomas

On 2010-08-17, The Quiet Center <thequietcenter (AT) gmail (DOT) com> wrote:
perhaps you want "any"

SELECT * FROM language WHERE langkey LIKE ANY ( SELECT acct_desc ||
'-%' FROM dbo.product WHERE acct_code LIKE 'WSDOM%');



--
¡spuɐɥ ou 'ɐꟽ ʞooꞀ

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Aug 18, 1:08*pm, Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com>
wrote:


he

Sybase

SQL Sewrver 2008 does. I have some other tricks with it to convert a
long parameter list into a table. See "VALUES() and Long Parameter
Lists (22 July 2010) at http://www.simple-talk.com/sql/learn...rameter-lists/

That syntax us techncially correct in ANSI/ISO SQL:2003 with a little
work :

SELECT * FROM foobar WHERE langkey LIKE ANY ((SELECT acct_desc ||
'-%' FROM Products WHERE acct_code LIKE 'WSDOM%'));

LANGUAGE is a reserved word and you nee to make the subquery into an
expression.