Concatenating or embedding wildcards in SQL LIKE

[vjp2.at]

Can I put the % inside the data?
Can I concatenate the % with a variable?

IE select a from x where a.w like "%qwerty%"
vs like c with c="%qwerty%"
or like %+c+%
??



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]

 

[Marshall Barton]

Can I put the % inside the data?
Can I concatenate the % with a variable?

IE select a from x where a.w like "%qwerty%"
vs like c with c="%qwerty%"
or like %+c+%

Yes. but try it and see what happens.

 

[vjp2.at]

*+->IE select a from x where a.w like "%qwerty%"
*+->vs like c with c="%qwerty%"
*+->or like %+c+%

*+-Yes. but try it and see what happens.

I put the "%" inside the data table and it din't work.
I also tried Concatenate(firstname," ",lastname) = x (or like x)
on something else and it didn't work so I imgine Concatenate("%",x,"%")
won't either. However, I got a reasonable test result with x=y.
Not optimal, tho if I expand to the full project size.

- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]

 

[Marshall Barton]

*+->IE select a from x where a.w like "%qwerty%"
*+->vs like c with c="%qwerty%"
*+->or like %+c+%

*+-Yes. but try it and see what happens.

I put the "%" inside the data table and it din't work.
I also tried Concatenate(firstname," ",lastname) = x (or like x)
on something else and it didn't work so I imgine Concatenate("%",x,"%")
won't either. However, I got a reasonable test result with x=y.
Not optimal, tho if I expand to the full project size.

If we're not speaking two different languages,
select a from x where a.w like "%qwerty%"
should work fine (if you're using ADO).

In some cases,
like c
will also work (c="%qwerty%"), depending on the scope of c
(no if c is a VBA variable, yes if c is a field in a source
table, yes if it's a query parameter).

The same is true for
like "%" & c & "%"
(where c="qwerty") and the same scope rules for c.

Note that for DAO, you need to use * instead of %

 

[John Spencer]

I'm not sure I understand the question.

Are you trying to search for the presence of "%" in a field? Or are you
trying to use "%" as a wildcard character?

IF you are attempting to search for the presence of the "%" character, then
you would need to tell the engine that you were looking for the % character
by surrounding it with square brackets.

Find records where SomeField contains a "%" character
SomeField Like '%[%]%"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Also in some flavors of SQL you can use the escape Clause
FROM MS TRANSACT SQL HELP

Pattern Matching with the Escape Clause

You can search for character strings that include one or more of the special
wildcard characters. For example, the discounts table in the customers
database may store discount values that include a percent sign (%). To
search for the percent sign as a character instead of as a wildcard
character, the ESCAPE keyword and escape character must be provided. For
example, a sample database contains a column named comment that contains the
text 30%. To search for any rows containing the string 30% anywhere in the
comment column, specify a WHERE clause of WHERE comment LIKE '%30!%%' ESCAPE
'!'. Unless ESCAPE and the escape character are specified, SQL Server
returns any rows with the string 30.

This example shows how to search for the string "50% off when 100 or more
copies are purchased" in the notes column of the titles table in the pubs
database:

SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased'
ESCAPE '%'

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

select a from x where a.w like "%qwerty%"
should work fine (if you're usingADO).

Note that for DAO, you need to use * instead of %

For Jet, instead of LIKE use ALIKE, then you can use % regardless of
ADO or DAO, or rather, regardless of ANSI Query Mode.

DAO *always* uses Jet's ANSI-89 Query Mode. ADO *always* uses ANSI-92
Query Mode. The Access interface can use either ANSI Query Mode i.e.
can be explicitly set at the Access application level. ALIKE allows
you to make your Jet SQL code ANSI Query Mode neutral.

Jamie.

--