do I need to add quotation marks when adding a string variable to a MySQL SELECT using REGEXP?

Question

On a site I'm doing a user can enter a search string like

 "do you have foo bar"

I'm using Coldfusion8 to create a |-delimited list like so:

<cfif len(LOCAL.Search.s_artikelbezeichnung) GT 0>
    <cfloop list="#LOCAL.Search.s_artikelbezeichnung#" delimiters=", " index="terms">
        <cfset variables.searchTerms = variables.searchTerms & terms & '|'>;
    </cfloop>   
    <cfset variables.searchTerms = Left(variables.searchTerms, len(variables.searchTerms)-1)>
</cfif>

Which should give me:

 do|you|have|foo|bar    // param_artikelbezeichnung

In MySQL I'm then doing a REGEX:

...
AND ( (param_artikelbezeichnung = '') AND (1=1) 
    OR (
        a.artikelbezeichnung REGEXP param_artikelbezeichnung 
        )

My Question:
Do I have to add quotation marks '' around my variable or can I enter it like this? I have a bunch of dynamic criteria I need to add to a search, so I'm looking for some general info.

Thanks for help!

EDIT:
Ok this is what I'm getting from the database:

  a.artikelbezeichnung REGEXP param_artikelbezeichnung 
     >> using foo|bar OK
  a.artikelbezeichnung REGEXP param_artikelbezeichnung
     >> not passing a value > ERROR

So I don't need quotation marks, but I need to pass something to the regex to not produce an error on empty fields. Would something like this work:

   AND ( (param_artikelbezeichnung = '') AND (0=1) 
      OR (  a.artikelbezeichnung REGEXP param_artikelbezeichnung )

Almost another question. Thanks for answering!

simbabque · Accepted Answer · 2012-06-26T12:14:46.650

1

According to the mysql doc you need to put the search string/regex in single quotes like this:

AND ( (param_artikelbezeichnung = '') AND (1=1) 
    OR (
        a.artikelbezeichnung REGEXP 'do|you|have|foo|bar'
        )

Otherwise it will produce a syntax error in mysql.

Update:

Since you're in a stored procedure, you can do it like this. For other readers: param_artikelbezeichnung is an argument of the stored procedure which is called by coldfusion.

SELECT COUNT(a.id) AS recs, a.artikelnummer, a.nos, a.nos_anzeige
FROM artikel AS a
WHERE a.aktiv = 'ja'
AND a.artikelbezeichnung REGEXP IF(artikelbezeichnung = '', '.', artikelbezeichnung )

edited Jun 26 '12 at 12:14

answered Jun 26 '12 at 08:37

simbabque

53,749
8
73
136

Ok, so I could have to add this to a CONCAT like this: CONCAT('"',a.artikelbezeichnung,'|$"')? – frequent Jun 26 '12 at 08:47
I thought `artikelbezeichnung` is the field you want your regex to match against. What would the `CONCAT` achieve? Which programing language do you use to create and send your SQL queries? Just put the pattern in the same way a normal `artikelnummer='$artikelnummer'` would look like. – simbabque Jun 26 '12 at 09:29
You are correct. I'm fighting with this right now... see me edit in 2mins – frequent Jun 26 '12 at 09:31
Ich verstehe dein Problem immer noch nicht. Baust du das in Coldfusion? ist der `param_artikelbezeichnung` eine Variable? Ich kenne Coldfusion leider nicht. Gibt es da auto-quoting? - I still don't undersand your problem. Do you do this in Coldfusion? Is `param_artikelbezeichnung` a variable? I'm not familiar with coldfusion. Does it do auto-quoting for vars in queries? – simbabque Jun 26 '12 at 09:36
Ah. Wir sprechen eine Sprache. Da Abfrage wurde in Coldfusion gebaut, aber ich muss sie in MySQL übernehmen. In Coldfusion wird der Regexp nur ausgeführt, wenn der User etwas bei a.artikelbezeichnung eingebenen hat. Also a.artikelbezeichnung REGEX... Ich muss in MySQL also prüfen, ob das Feld leer ist. Wenn ja (Feld leer), wollte ich (1=1) setzen, also alle Artikel auswählen, wenn Nein (Feld belegt), dann Regex – frequent Jun 26 '12 at 09:41
denke gerade an if not null. moment – frequent Jun 26 '12 at 09:42
We do, but the others don't so also post this in English please. – simbabque Jun 26 '12 at 09:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13039/discussion-between-simbabque-and-frequent) – simbabque Jun 26 '12 at 09:43

score 0 · Answer 2 · answered Jun 26 '12 at 08:29

0

If you are putting it in Quotes, the engine will treat it as a string, else its value at that row.

answered Jun 26 '12 at 08:29

Sashi Kant

13,277
9
44
71

The end result should be checking for REGEXP do|you|have|foo|bar, so give back all records that have either do, you, have, foo or bar in them. See here: http://stackoverflow.com/questions/1127088/mysql-like-in. I was wondering if I need to add the quotation marks "by hand" – frequent Jun 26 '12 at 08:33
That depends on the way you build your SQL, or rather the language/technology you are using. – simbabque Jun 26 '12 at 08:38

do I need to add quotation marks when adding a string variable to a MySQL SELECT using REGEXP?

2 Answers2