select where string doesn't match exactly

Question

It's my table rows:

++ id ---- text ++++++++++++++
-- 1  ---- '90','80,'50' -----
-- 2  ---- '30','2','1_2' --
-- 3  ---- '10_2','5_3' -----

as you see, text contains 2 types of numbers, one doesn't have underscore, and the other does.

I want to select rows which have at least one number without underscore (type 1). Something like this: (result-set)

++ id ---- text ++++++++++++++
-- 1  ---- '90','80,'50' -----
-- 2  ---- '30','2','1_2' --

(3 is ignored)

How to do that? (I think it's possible with NOT LIKE, but I don't know how to write)

shomel · Answer 1 · 2012-10-22T15:30:08.103

2

How long may be your numbers? Try this:

SELECT t1.id,t1.txt FROM t t1, t t2 WHERE t1.txt LIKE "%'__'%" AND t2.txt NOT LIKE "%\__',%"

edited Oct 22 '12 at 15:30

answered Oct 22 '12 at 13:32

shomel

196
1
10

score 1 · Answer 2 · answered Oct 21 '12 at 00:25

1

You can't do it with LIKE, but you can with a RLIKE, which uses regular expressions:

select * from mytable
where `text` rlike "'\d+_\d+'"

answered Oct 21 '12 at 00:25

Bohemian

412,405
93
575
722

I think your expression will select `2` and `3`, but I want to select just `3`... (in `3`, all numbers have an underscore, but in `2`, some don't) – mrdaliri Oct 21 '12 at 21:06

score 1 · Accepted Answer · answered Oct 22 '12 at 13:11

The below query counts the number of commas in the strings, number of distinct numbers can be calculated as 1 more than the number of commas as the numbers are separated by commas, number of underscores in the string:

select id,
len(text) - len(replace(text,',','')) as count_of_commas,
len(text) - len(replace(text,',','')) + 1 as count_of_number,
len(text) - len(replace(text,'_','')) as count_of_underscore,
len(text) - len(replace(text,',','')) + 1 - (len(text) - len(replace(text,'_',''))) as zero_if_no_number_without_underscore_exists
from t1

The above query gives the following results:

enter image description here

Now using the logic of above query, following query can be used to get the desired result:

select * from t1
where len(text) - len(replace(text,',','')) + 1 - (len(text) - len(replace(text,'_',''))) != 0

i.e. it returns the rows where atleast one number exists without the underscore.

Thanks! It works perfect.. (Note: `LEN` doesn't exist in MySQL 5.1, it replaced with `LENGTH`... — mrdaliri, Oct 22 '12 at 17:17

select where string doesn't match exactly

3 Answers3