select values which contains specific characters in mysql

Question

I have a database table which contains questions in hindi and english language in same column but are separated by keyword [en] english language [en:] and [hi] hindi language [hi:] and some of them are not separated by language tags

I want to select only that questions which have [en] [en:] and [hi][hi:] tags in mysql

Are the tags part of the question? or a separate field?

Ravinder Reddy
– Ravinder Reddy

2014-07-25 10:12:02 +00:00
Commented Jul 25, 2014 at 10:12 — Ravinder Reddy
– Ravinder Reddy, Commented Jul 25, 2014 at 10:12
Post a sample question with tag included in it

Ravinder Reddy
– Ravinder Reddy

2014-07-25 10:12:58 +00:00
Commented Jul 25, 2014 at 10:12 — Ravinder Reddy
– Ravinder Reddy, Commented Jul 25, 2014 at 10:12
tegs are part of question

Rahul Gahlot
– Rahul Gahlot

2014-07-25 10:14:34 +00:00
Commented Jul 25, 2014 at 10:14 — Rahul Gahlot
– Rahul Gahlot, Commented Jul 25, 2014 at 10:14

Ravinder Reddy · Accepted Answer · 2014-07-25 10:15:26Z

3

Select * from table_name
 where question like '%[en]%' or 
       question like '%[en:]%' or 
       question like '%[hi]%' or 
       question like '%[hi:]%'

If the start tag is of format '[en]' and end tag is of format '[en:]', then

Select * from table_name
 where question like '%[en]%[en:]' or 
       question like '%[hi]%[hi:]'

answered Jul 25, 2014 at 10:15

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

5 Comments

RobIII Over a year ago

If the start tag is of format '[en]' and end tag is of format '[en:]', then > The question states: I want to select only that questions which have [en] [en:] and [hi][hi:] tags in mysql

Ravinder Reddy Over a year ago

I added [en] as one example. I know that there exists other formats like [hi]. See my answer as well

RobIII Over a year ago

Point is that your query in your answer matches questions that have either [en]foo[en:] or [hi]bar[hi:]. Rahul wants to match questions that have both. For example [en]foo[en:] [xx]whoop![xx:] [hi]bar[hi:] should match but not [en]foo[en:] [xx]whoop![xx:] (at least: that's how I understand the question)

Ravinder Reddy Over a year ago

I am not sure if the same row contains both versions of questions in the same column. OP though claims in the same column, I fear it is not same row.

RobIII Over a year ago

Ah that would make (a bit :P ) more sense. Also: did you 'miss' the wildcard(s) at the end of your second queries on purpose? Shouldn't like '%[en]%[en:]' or ... be like '%[en]%[en:]%' or ...? If OP could clarify the data in the table a like '[en]%[en:]' or... might even suffice which might actually even use indexes...

RobIII · Accepted Answer · 2014-07-25 10:14:30Z

2

Select *
From mytable
Where mycolumn like '%[en]%[en:]%'
    and mycolumn like '%[hi]%[hi:]%'

Note that this query will not be very performant but that's what you get for not normalizing your database.

You could use a regex match to ensure not finding 'tags' that contain other tags like [en]foo[hi]bar[en:] but that would make performance even worse.

answered Jul 25, 2014 at 10:14

RobIII

8,8414 gold badges46 silver badges99 bronze badges

Comments

user956584 · Accepted Answer · 2014-07-25 10:24:29Z

0

Add another column eq enum type EN => [Y,N] HI [Y,N], you can use query like in other questions to update database. So its more optimal that use like search every time

edited Jul 25, 2014 at 10:24

answered Jul 25, 2014 at 10:18

user956584

5,5875 gold badges44 silver badges51 bronze badges

3 Comments

RobIII Over a year ago

Nonsense; INNODB supports LIKE queries just as well as MyISAM.

user956584 Over a year ago

Ye true, i made a mistake i mean full text search

RobIII Over a year ago

INNODB supports full text search just as well (just like MyISAM). Also adding another column like you suggest is NOT the way to go. OP needs to add a column 'language_id' and add another table with languages and use FK's like they're intended to.

billyl320 · Accepted Answer · 2017-11-10 22:20:52Z

0

I found this to work

Select * from table_name
       where question like '%en%'

answered Nov 10, 2017 at 22:20

billyl320

621 silver badge9 bronze badges

Collectives™ on Stack Overflow

select values which contains specific characters in mysql

4 Answers 4

5 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related