2

this is an example of the regex using datatables that I like. e.g. enter ^[S,A] in the global search and tick Treat as regex and this gives all the Columns that Start with A or S and this is what I want to achieve. not quiet sure how Use smart search works

And this is the server side processing example

here is a basic datatables fiddle

Can I combine these so that I can do a regex in datatables that works on the server side processing?

As a first I have tried something like this on my db but not sure if it can be done here before trying to do it in datatables. SELECT employee_name, employee_salary, employee_age FROM employee WHERE employee_name LIKE REGEXP '^[A,S]'

HattrickNZ
  • 4,373
  • 15
  • 54
  • 98
  • 1
    On an unrelated note, you might want to change your regex to `^[AS]`. Your current regex will give results that start with a `,` as well. – Harsh Poddar May 06 '16 at 03:14
  • 1
    Remove the `LIKE`, you only need the `REGEXP`. See [4.3.4.7 Pattern Matching](https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html) – Shaw May 06 '16 at 03:21
  • @Harsh Good point. that is my lack of knowledge in regex. – HattrickNZ May 06 '16 at 03:45

1 Answers1

1
SELECT employee_name, employee_salary, employee_age FROM employee
WHERE employee_name LIKE REGEXP '^[A,S]'

This will do not give what you want, and should get a syntax error. As @Harsh Poddar said, it will also match column started with ,, so the regexp expression is just '^[AS]'.

Anyway, if you want query all of columns started with A or S, it will help by followed sql;)

SELECT employee_name, employee_salary, employee_age FROM employee
WHERE employee_name REGEXP '^[AS]'

This is not similiar with followed sql,

SELECT employee_name, employee_salary, employee_age FROM employee
WHERE employee_name LIKE '%A%'

If you want use LIKE, it should be like this as least,

SELECT employee_name, employee_salary, employee_age FROM employee
WHERE employee_name LIKE 'A%' or employee_name LIKE 'S%'

And if you want more character start, you need to add more LIKE condition in WHERE clause, so it seems to be more concise for you to write a query sql when you use REGEXP.
About performance, take a look of this.

Community
  • 1
  • 1
Mippy
  • 364
  • 2
  • 11
  • ultimately I want to get it working with datatables but is `SELECT employee_name, employee_salary, employee_age FROM employee WHERE employee_name REGEXP '^[AS]'` the same as `SELECT employee_name, employee_salary, employee_age FROM employee WHERE employee_name LIKE '%A%'` or at least very similar, with the REGEXP more powerful? – HattrickNZ May 06 '16 at 03:44