Extracting Text between sub strings in MySQL

Question

I have a text like the below

[:de]

Die BB2 ist eine erlesene türkische Gulet in klassischer Holzbauweise, die alle Annehmlichkeiten und die Eleganz einer exklusiven Yacht, die durch die hohe Qualität der vorzüglichen Holzverarbeitung und das anspruchsvolle Design untermalt wird. Auf 37 m Länge bietet Sie Ihnen 15 moderne, klimatisierte, große Kabinen mit Duschkabine / WC. Salon und Deck sind ausgesprochen großzügig angelegt und mit edlem Mobiliar ausgestattet. Die professionelle und erfahrene Crew ist auf das Beste damit vertraut für die Sicherheit und Zufriedenheit der Gäste zu sorgen und Ihnen einen Rundumservice zu bieten.

[:en]
At 37 x 10 m the BB2 is designed to portray elegant style with a relaxing atmosphere. She is one of the most perfect examples of the Turkish wooden gulets with all amenities of an exclusive yacht. 15 modern, air-conditioned cabins with shower and toilet are on offer for the guests on board. The salon and deck are spaciously designed and equipped for your comfort. The professional and experienced crew is more than familiar in making sure the guests feel safe and comfortable and offer a great service all around.
[:]

[:en] represents English text and [:de] represents German text

want output for a language in MySQL. What will be the query to extract text in German only?

Your question is not clear. Is this text in a column in a MySQL database? Are you attempting to parse the contents of the field such that only the English or German content is returned? To find an answer on Stackoverflow your questions must be specific, and this is very general. — Tim O'Brien
– Tim O'Brien, Commented Aug 14, 2017 at 12:46

Gordon Linoff · Accepted Answer · 2017-08-14 12:38:48Z

1

Assuming this is your data with the german always before the english, then:

select substring_index(substring_index(col, '[:de]', -1), '[:en]', 1)

If this is not the case, you can test for different possibilities:

select (case when col like '%[:de]%[%:en]%'
             then substring_index(substring_index(col, '[:de]', -1), '[:en]', 1)
             when col like '%[:de]%[%:]%'
             then substring_index(substring_index(col, '[:de]', -1), '[:]', 1)
             else substring_index(col, '[:de]', -1)
       end)

answered Aug 14, 2017 at 12:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Dhiraj M Over a year ago

Thanks Gordon, you made my day !

Collectives™ on Stack Overflow

Extracting Text between sub strings in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related