Is there any function for finding the result with special character in a sql like?

Question

I'm try to get data from SQL using LIKE successful to get data but the result more than I need

Here is my some data

apple1
apple2
apple3
applejuice1
applej1
applej2

My query:

select * from apple where name like '%applej%'

Currently I got:

applejuice1
applej1
applej2

My Expected output:

applej1
applej2

There are some answers involving using regular expressions. While that is useful for complex matching, I still would advise to use the LIKE operator's `_` wildcard character when possible. Regex expressions can be a performance killer and I am not sure if regex expressions can be used by indexes as well. (I know the LIKE operator can often make use of indexes, which is very preferrable.) — Bart Hofland, Jun 17 '19 at 07:03
Can you explain that further? What do you mean by that "special character" in your title? — Nico Haase, Jun 17 '19 at 07:16

score 4 · Answer 1 · answered Jun 17 '19 at 06:58

4

You can try this,

SELECT * FROM apple where name LIKE '%applej_'

'_' is used to represent a single character in SQL server. For MS Access you can try this

SELECT * FROM apple where name LIKE '*applej?'

answered Jun 17 '19 at 06:58

ritheesh

i don't want applejuice1, but your sql will return it – Tommy Tang Jun 17 '19 at 07:10
@TommyTang I doubt that very much... `SELECT * FROM apple where name LIKE '%applej_'` should work just fine. (It works fine on my MariaDB instance, at least...) – Bart Hofland Jun 17 '19 at 07:19
but if my data are applej1, applej15? – Tommy Tang Jun 17 '19 at 07:24
@TommyTang Yes, then it becomes more complex. The `_` wildcard represents just a single character, so to match `15`, you would need to use two of them. But that would also match `1a`, for example. And if you want to match `apple123456` with `'apple______'`, it will include `applejuice1` again. Here, you probably want to use a regex indeed. – Bart Hofland Jun 17 '19 at 07:27
@TommyTang if you want to get applej15 or any two digit integer after applej you can use `SELECT * FROM apple where name LIKE '%applej[0-9][0-9]'` or if you dont want to use any alphabets after applej you can do that using `'applej[^a-z]'` (`[]` represents single character) – ritheesh Jun 17 '19 at 07:42

Arulkumar · Accepted Answer · 2019-06-17T07:14:43.787

3

Using REGEXP this is possbile:

select * 
from apple 
where `name` REGEXP '^applej[0-9]'

Update:

If the name has data as applej1, applej2, applej15, applej109, the following query will work:

select * 
from apple 
where `name` REGEXP '^applej[0-9]+'

edited Jun 17 '19 at 07:14

answered Jun 17 '19 at 06:56

Arulkumar

score 2 · Answer 3 · answered Jun 17 '19 at 06:52

You can use a regular expression, this will include all rows containing 'applej' + 0 or 1 more character

SELECT * FROM test WHERE  col1 REGEXP '^applej.?$'

This one will find all rows containing 'applej' + exactly 1 more character

SELECT * FROM test WHERE  col1 REGEXP '^applej.{1}$'

And if the number after 'applej' might contain several digits

SELECT * FROM test WHERE  col1 REGEXP '^applej[0-9]+$'

Of course maybe the LIKE suggested by @forpas in the comments above is all you need

score 0 · Answer 4 · edited Jun 17 '19 at 07:16

0

i found the solution with

SELECT * FROM apple WHERE MATCH(name) AGAINST('applej')

edited Jun 17 '19 at 07:16

forpas

answered Jun 17 '19 at 07:08

Tommy Tang

A full-text search... Yes, that might do the job as well. You may want to check it out somewhat further to decide if it really fits your needs in all respects: https://stackoverflow.com/questions/224714/what-is-full-text-search-vs-like – Bart Hofland Jun 17 '19 at 07:15

4 Answers4