0

Lets say the words in tables are :

  • ab astest
  • ab astestabc
  • ab as
  • ablxyx pqr tpl
  • ab as ablmnr
  • wasab as ablmnr
  • vbn ab as abltere

I need to match where the starting word should be : "ab as" ( Including space ) and then infinity ( that is n no. of combinations or characters )

The result should look under 2 filters :

1) Include space : Starting with "ab as" and any no. of words or characters attached

Result :

ab as ablxyx pqr tpl

ab as ablmnr

2) Exclude space : Starting with "ab as" and any no. of characters and words attached to this string without space

Result :

ab astest

ab astestabc

The results should be mutually exclusive.

Can anyone share me the Mysql REGEX expression for the same?

Gaurav Bisht
  • 11
  • 1

1 Answers1

0

You can use REGEXP for that

CREATE TABLE textlist
    (`text` varchar(17))
;

INSERT INTO textlist
    (`text`)
VALUES
    ('ab astest'),
    ('ab astestabc'),
    ('ab as'),
    ('ablxyx pqr tpl'),
    ('ab as ablmnr'),
    ('wasab as ablmnr'),
    ('vbn ab as abltere')
;

✓

✓

SELECT DISTINCT SUBSTR(`text`,POSITION("ab as" IN `text`),LENGTH(`text`)) 
FROM textlist WHERE `text` REGEXP 'ab as[[:blank:]]';

| SUBSTR(`text`,POSITION("ab as" IN `text`),LENGTH(`text`)) |
| :-------------------------------------------------------- |
| ab as ablmnr                                              |
| ab as abltere                                             |

SELECT DISTINCT SUBSTR(`text`,POSITION("ab as" IN `text`),LENGTH(`text`)) 
FROM textlist WHERE `text` REGEXP 'ab as[^[:blank:]]';

| SUBSTR(`text`,POSITION("ab as" IN `text`),LENGTH(`text`)) |
| :-------------------------------------------------------- |
| ab astest                                                 |
| ab astestabc                                              |

db<>fiddle here

nbk
  • 45,398
  • 8
  • 30
  • 47
  • How do we convert above mysql query into Laravel 5.4 eloquent ? Any help. – Gaurav Bisht Dec 03 '19 at 23:39
  • Please accept my answer and ask a new Question with laravel tag, and use https://stackoverflow.com/questions/42577045/laravel-5-4-validation-with-regex as starting point for your search the rest you will find in the documentation https://laravel.com/docs/5.4/validation#rule-regex. – nbk Dec 04 '19 at 00:17