RegExp or wildcard applied to cell value? 1

[tlhawkins]

hello,

I have a field that contains a text string that I need to match with wildcards or a Regular Expression.

The part I'm having trouble with is that the database field contains the wildcards and the input string has the full text.

For instance I could have the following SQL

Select *
from TableOddIds
where TableOddIds.IDmatch like "ABCD1234ABCD"

Where the string "ABCD1234ABCD" could be from various sources including User Input.

I would want that to match an IDmatch value of
"ABCD%ABCD"

The actual values in the DB now are more like
"ABCD CD" but I could fill in the blank spaces to make it easier (if that would make it easier)

Any ideas?
Thanks.

 

[TonyGroves]

You could simply switch the terms:
[tt]
SELECT *
FROM TableOddIds
WHERE "ABCD1234ABCD" LIKE IDmatch
[/tt]
The example you gave there is a wildcard pattern, which is tested using the LIKE operator. Since regular expressions have a different syntax to wildcard patterns, they are tested using RLIKE or REGEXP. If you wanted to extend your testing to include both syntax types, each record would have to indicate which syntax is being used. For example:
tt]
SELECT *
FROM TableOddIds
WHERE
(matchtype='L' and "ABCD1234ABCD" LIKE IDmatch)
OR (matchtype='R' and "ABCD1234ABCD" REGEXP IDmatch)
[/tt]

 

[TonyGroves]

You could simply switch the terms:

SELECT *
FROM TableOddIds
WHERE "ABCD1234ABCD" LIKE IDmatch

Slight formatting mistake there. Take 2:

The example you gave there is a wildcard pattern, which is tested using the LIKE operator. Since regular expressions have a different syntax to wildcard patterns, they are tested using RLIKE or REGEXP. If you wanted to extend your testing to include both syntax types, each record would have to indicate which syntax is being used. For example:
[tt]
SELECT *
FROM TableOddIds
WHERE
(matchtype='L' and "ABCD1234ABCD" LIKE IDmatch)
OR (matchtype='R' and "ABCD1234ABCD" REGEXP IDmatch)
[/tt]

 

[TonyGroves]

Third time lucky:
-----

You could simply switch the terms:
[tt]
SELECT *
FROM TableOddIds
WHERE "ABCD1234ABCD" LIKE IDmatch
[/tt]
The example you gave there is a wildcard pattern, which is tested using the LIKE operator. Since regular expressions have a different syntax to wildcard patterns, they are tested using RLIKE or REGEXP. If you wanted to extend your testing to include both syntax types, each record would have to indicate which syntax is being used. For example:
[tt]
SELECT *
FROM TableOddIds
WHERE
(matchtype='L' and "ABCD1234ABCD" LIKE IDmatch)
OR (matchtype='R' and "ABCD1234ABCD" REGEXP IDmatch)
[/tt]

 

[tlhawkins]

Thanks Tony,

I'll give that a try.

In the example you gave...

SELECT *
FROM TableOddIds
WHERE
(matchtype='L' and "ABCD1234ABCD" LIKE IDmatch)
OR (matchtype='R' and "ABCD1234ABCD" REGEXP IDmatch)

What would I have to do to the IDmatch field which now looks like this "ABC CD" to get it to match "ABCD1234ABCD" LIKE IDmatch? Do I need to replace the blank spaces with "_" or "%" or what?

Thanks.

 

[TonyGroves]

ABC%CD" will match. % indicates any number of characters; _ indicates one character.

 

[tlhawkins]

Thanks,

That did it!