-1

Is it possible to create a variable in MySql for a LIKE expression?

ex.

SET @test = '%my text to look for%';
SELECT * FROM MYTABLE WHERE MYCOLUMN LIKE @test;

Of course I tried this approach and it did not work.

Sorry for choosing the mysql-connect tag, couldn't really come up with anything else.

EDIT 1

"Did not work" means I don't get results with the variable as without.

Kermit
  • 33,827
  • 13
  • 85
  • 121
Steven Combs
  • 1,890
  • 6
  • 29
  • 54
  • 2
    Define "did not work". Did you get an error (in which case, we need to know what it was), or just no results (in which case you should check that there are actually rows that meet your search criteria). – Larry Lustig Sep 25 '12 at 14:17
  • *"Did not work" means did not work* - @meanbunny I think you may have a comprehension problem. Or something. – D'Arcy Rittich Sep 25 '12 at 14:22
  • I didnt know this was English 101? I thought this was a place to get help for programming problems. I have clearly laid out the information that needs to be known to answer this question. – Steven Combs Sep 25 '12 at 14:23
  • did you tried with '@test' instead of @test? – Soojoo Sep 25 '12 at 14:24
  • That did not work, thanks though! – Steven Combs Sep 25 '12 at 14:25
  • 1
    @meanbunny: It's about showing some basic effort when you ask for free help. "Did not work" doesn't tell us what research _you_ did to find out what _did_ happen and what more clues there are in your test results. It's also usually (though not in your case) the cause of an information paradox, where you try to explain what your program is _intended_ to do, solely by providing code that by definition does not perform that function, leaving only the phrase "it doesn't work" as a clue that we're to magically conjure your intent through telepathy and, where possible, expert intuition. Whew! – Lightness Races in Orbit Apr 02 '13 at 17:30
  • Right gotcha, thanks for the advice! – Steven Combs Apr 02 '13 at 21:04

1 Answers1

3

Yes, your example should work fine. See my test case below:

SQL Fiddle Example

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • That might pass sql syntax but that doesn't mean the query works against data. If I put in the exact text that I am looking for in place of my variable it works correctly. Placing it in the variable makes it fail. – Steven Combs Sep 25 '12 at 14:17
  • Please provide an example of your problem with [SQL Fiddle](http://sqlfiddle.com). – D'Arcy Rittich Sep 25 '12 at 14:19
  • 2
    @meanbunny - I tested the example on my MySQL instance and it works fine. What RedFilter wrote is true, it's both valid syntax and it does work. – N.B. Sep 25 '12 at 14:27
  • hmmm @N.B. well I will revisit and see what the problem is with my query. I have it just as in his example and it isn't working for me. – Steven Combs Sep 25 '12 at 14:29
  • Well, is your query working when you don't use the variable? – N.B. Sep 25 '12 at 14:31
  • Yes that is why I am confused at why the variable is breaking it. – Steven Combs Sep 25 '12 at 14:32
  • 1
    Are you doing this query from within MySQL prompt or GUI or is it via php or some other scripting language? Also, what's your MySQL version? – N.B. Sep 25 '12 at 14:33
  • I am using the Workbench, and my MySQL version is: mysql Ver 14.14 Distrib 5.5.24, for debian-linux-gnu (i686) using readline 6.2 – Steven Combs Sep 25 '12 at 14:37
  • 1
    What happens if you use the following from the workbench: `SET @test = "'%text to look for%'"; SET @st = "SELECT * FROM your_table WHERE column LIKE "; SET @stmt = CONCAT(@st, @test); PREPARE stmt FROM @stmt; EXECUTE stmt ;` – N.B. Sep 25 '12 at 14:38
  • That works...and his example is working now....had to be a user error or something. Not sure what though. Thank you very much @N.B.. – Steven Combs Sep 25 '12 at 14:43
  • Well, it appears it's 100% PICNIC, definitely no error in the code you posted. – N.B. Sep 25 '12 at 14:44