Regular expression query

[JonasLAX]

Hi everybody,

 

I have a table with data like this:

 

Dublin

Dublin Gallery

Dublin Gallery10004941

Berlin

Berlin Shop

Berlin Shop10002938

 

I would like to select "Dublin Gallery10004941" and "Berlin Shop10002938" because for both there are entries with exactly the same name but without the 8-digit number at the end (it always is 8 digits). Can anybody help me with this query?

 

Thanks,

Jonas

[webref.eu]

What will the ultimate purpose of this query be?  i.e. why do you need to do it? 

 

I suggest you write some pseudo code logic, i.e. write out the logic that you want your programme to follow, break it down into small steps, and then maybe people on here will be able to help you with executing each step. 

 

Rgds

[Maq]

SELECT * FROM my_table WHERE my_field REGEXP '^[a-zA-Z\s]+\d{8}$';

 

- Assumes there's no space after the last word (in your examples, "Shop" & "Gallery").

- Assumes the digits have to exactly 8 digits in length.

- Will match on something like, "Dublin123456578".

 

(I'm not sure how to flag case-insensitivity in the MySQL regex world, that's why I included "a-zA-Z")

 

Good luck.

[Ken2k7]

Maq - REGEX is case-insensitive. REGEX BINARY is case-sensitive.