MySQL using like to match specific string

Question

In my column I can have either a string like : "data+" or "data+data+data+..(undefined times)..+" I simply need to get the column where I have multiples data and not only one.

I tried with

mycol NOT LIKE '%+'

But it didn't work...

Actually I don't know the data it is a string that varies : 'blabla' or 'aString' or 'whatever'

If I had in my columns

'jdsjpgsg+jdsjpgsg+', 'dvgff+', 'ffef+eefds+ghghgh+'

I want to select only

'jdsjpgsg+jdsjpgsg+',
'ffef+eefds+ghghgh+',

NOT 'dvgff+' !

Try mycol NOT LIKE 'data+%'

Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman

2018-06-08 08:27:37 +00:00
Commented Jun 8, 2018 at 8:27 — Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman, Commented Jun 8, 2018 at 8:27

Wei Lin · Accepted Answer · 2018-06-08 08:57:45Z

1

if you want to search '..xxx+..' then you should be use xxx+%

if you want to search '..+xxx..' then you should be use %+xxx

if you want to search '..++..' then you should be use %++%

if you want to search '..+..+..' then you should be use %+%+%

It is what I get too and I dont want that. It is actually what i don't want to select. If I had jdsjpgsg+jdsjpgsg+ in my table I want to select it and NOT jdsjpgsg+ It is tricky...

so you can try like '%+%+%' to exclude just one '+'

CREATE TABLE TestTable
    (`text` varchar(90))
;

INSERT INTO TestTable
    (`text`)
VALUES
    ('jdsjpgsg+jdsjpgsg+'),
    ('dvgff+'),
    ('ffef+eefds+ghghgh+')
;

select * from TestTable
where text like '%+%+%'

|               text |
|--------------------|
| jdsjpgsg+jdsjpgsg+ |
| ffef+eefds+ghghgh+ |

SQL Fiddle Demo Link

edited Jun 8, 2018 at 8:57

answered Jun 8, 2018 at 8:30

Wei Lin

3,8213 gold badges27 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benoît S Over a year ago

It is what I get too and I dont want that. It is actually what i don't want to select. If I had jdsjpgsg+jdsjpgsg+ in my table I want to select it and NOT jdsjpgsg+ It is tricky....

Praveen Kumar Purushothaman · Accepted Answer · 2018-06-08 08:28:53Z

0

The % is the wildcard character. You should be using the % after data. Try like:

SELECT * FROM `table` WHERE `mycol` NOT LIKE 'data+%'

The above query will filter out all the records that have any characters after data+.

answered Jun 8, 2018 at 8:28

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

Collectives™ on Stack Overflow

MySQL using like to match specific string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related