SQL TRICKS

 

JOIN TWO TABLE WITH GROUP BY:

SELECT q.exam_category_id, count(q.exam_category_id) as count, c.title from Questions as q

      LEFT JOIN ${Tables.examCategory} as c ON q.exam_category_id=c.id

      WHERE q.subject_id='$subjectId' AND q.table_id=1 GROUP BY q.exam_category_id ORDER BY c.`order`

NESTED SELECT:

SELECT * FROM quiz_subjecthree where id in (SELECT DISTINCT sub_subject_three_id from Questions WHERE subject_id='${subject}' AND exam_category_id='${category}' AND exam_category_one_id='${c1}' AND table_id=1 order by exam_year DESC)

SQL REGEX

How to use Regex in SQL (dataschool.com)

regex practice (in js replace group=>() with $1 in sql \1)

RegExr: Learn, Build, & Test RegEx

SELECT (column name)
FROM (table name)
WHERE (column name) ~* 'fire';

SELECT column FROM

  TABLE

WHERE

  column ~* '(.*)[\.|-]২০(\d\d)';

  

UPDATE

  TABLE

SET

  column = regexp_replace(

    column , '(.*)[\.|-]২০(\d\d)', '\2@\1▪\2'

  );