Wildcards

27 Jul 2006 3:40 PM

robdken

Hi I was just wondering if anyone can clear something up for me... I am
trying to write a SELECT query of the form:

DECLARE @var as varchar(5)
SET @var = (SELECT col1 FROM table1 WHERE id = 3)

SELECT * FROM table2
WHERE col2 LIKE '(@var)%'

....where % is a wild card. Is this possible, i can't get it to work?

Thanks in advance.

27 Jul 2006 3:50 PM

Aaron Bertrand [SQL Server MVP]

DECLARE @var VARCHAR(5);
SELECT @var = col1 FROM table1 WHERE id = 3;
SELECT * FROM table2 WHERE col2 LIKE @var + '%';

Show quote

"robdken" <robin.kenn***@hotmail.co.uk> wrote in message
news:1154014800.321001.75030@s13g2000cwa.googlegroups.com...
> Hi I was just wondering if anyone can clear something up for me... I am
> trying to write a SELECT query of the form:
>
> DECLARE @var as varchar(5)
> SET @var = (SELECT col1 FROM table1 WHERE id = 3)
>
> SELECT * FROM table2
> WHERE col2 LIKE '(@var)%'
>
> ...where % is a wild card. Is this possible, i can't get it to work?
>
> Thanks in advance.
>

27 Jul 2006 3:55 PM

Vern Rabe

Try this:

SELECT *
FROM table2
WHERE col2 LIKE @var + '%';

HTH
Vern Rabe

Show quote

"robdken" wrote:

> Hi I was just wondering if anyone can clear something up for me... I am
> trying to write a SELECT query of the form:
>
> DECLARE @var as varchar(5)
> SET @var = (SELECT col1 FROM table1 WHERE id = 3)
>
> SELECT * FROM table2
> WHERE col2 LIKE '(@var)%'
>
> ....where % is a wild card. Is this possible, i can't get it to work?
>
> Thanks in advance.
>
>

27 Jul 2006 3:57 PM

Roy Harvey

So many unknowns.

table1.col1 could be char with trailing blanks.
table1.col1 could be 5 characters long.
table2.col2 might not have any matches.

Roy

Show quote

On 27 Jul 2006 08:40:00 -0700, "robdken" <robin.kenn***@hotmail.co.uk>
wrote:

>Hi I was just wondering if anyone can clear something up for me... I am
>trying to write a SELECT query of the form:
>
>DECLARE @var as varchar(5)
>SET @var = (SELECT col1 FROM table1 WHERE id = 3)
>
>SELECT * FROM table2
>WHERE col2 LIKE '(@var)%'
>
>...where % is a wild card. Is this possible, i can't get it to work?
>
>Thanks in advance.

27 Jul 2006 3:59 PM

Tracy McKibben

robdken wrote:

> Hi I was just wondering if anyone can clear something up for me... I am
> trying to write a SELECT query of the form:
>
> DECLARE @var as varchar(5)
> SET @var = (SELECT col1 FROM table1 WHERE id = 3)
>
> SELECT * FROM table2
> WHERE col2 LIKE '(@var)%'
>
> ...where % is a wild card. Is this possible, i can't get it to work?
>
> Thanks in advance.
>

Done as one query:

SELECT table2.*
FROM table2
INNER JOIN table1
ON table2.col2 LIKE RTRIM(table1.col1) + '%'
WHERE table1.id = 3

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com