MySQL wildcards

[pr0pensity]

If I wanted to use 'x' as a wildcard, so that "select where LIKE 'abcde'" matches rows containing 'abcxe' and 'abcdx' but not 'abcyz'

Is there a way to do this directly in a query?

 

[mikeblas]

So you want the row to contain a definable wildcard? That isn't directly supported -- it isn't a part of the SQL language.

You can cook up a query to do it, though.

 

[pr0pensity]

How?

 

[surazal]

To match a single character in SQL, you would use the wildcard _

So the query would be somthing like:
select * from table_name where (column_name LIKE 'abc_e') OR (column_name LIKE 'abcd_')

Depending on how complex your query becomes, it might be better to regular expressions. Check with your db documentation on how they handle regular expressions in sql.

 

[ameoba]

You might end up doing something with regular expressions.

...or you could think about what it is you're really trying to do and restructure you data to make it simpler to do.

 

[mikeblas]

use the REPLACE intrinsic function to make your desired wildcard character an underline. Then, use the result of that expression as the pattern for the LIKE operator, then provide your anchor. Like this:

create table pr0pensity (col varchar(25))
go

insert into pr0pensity (col) values ('abcxe')
insert into pr0pensity (col) values ('nomatch')
insert into pr0pensity (col) values ('abcyz')
go

select * from pr0pensity where 'abcde' like REPLACE(col, 'x', '_')
go

col
-------------------------
abcxe

(1 row(s) affected)

Does it go without saying that this is a shitty query? It'll always do a full table scan. If there's anything at all you can do with your model or your data to avoid this kind of query, you should do so.

 

[pr0pensity]

I was considering adding an extra column that maps where the wildcards appear in the text.

 

[pr0pensity]

I need it so the table contains the wildcard, not the query. The exact character doesn't mattter.

 

[mikeblas]

I need it so the table contains the wildcard, not the query. The exact character doesn't mattter.

If you'd like the table to contain the wildcard, use the query I showed in Post #6 and simply substitute the name of the column with the wildcard chracter for 'x'. Please let me know if you have specific questions about that method.

 

[debaser_]

IIRC the % operator is used for wildcards. If you wanted to do something that searched for all strings that had the name John in them you would do "LIKE %John%". If you only wanted to have John as a first name you would probably use John%. Im too lazy to look it up and its been a while since ive messed with any database stuff.

 

[ameoba]

% = any sequence of characters (including 0 characters)
_ = any single character