CODEWITHSUNDEEP
mysqlsqlselectsubstring
Hommer Smith
Hommer Smith

Reputation: 27862

Finding language in string (doing substring?) with MySQL

I have a table with languages which s_name value looks like this:

'en_UK'
'en_US'
'de_CH'
'de_AT'

I want to get all the distinct languages, without the country part. So for example, in case I just had those of the example, I would need to get:

en
de

What would be the best way of doing so?

I have this right now:

SELECT DISTINCT SUBSTRING(name,1,2) 
FROM my_languages

Upvotes: 1

Views: 111

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this:

SELECT DISTINCT SUBSTRING_INDEX(name, '_', 1) langName 
FROM my_languages

OR

SELECT SUBSTRING_INDEX(name, '_', 1) langName 
FROM my_languages 
GROUP BY langName

Check this link MySQL STRING Functions

Upvotes: 1

Art
Art

Reputation: 5792

This will work in Oracle as well as in MySql I think:

SELECT SUBSTR('en_UK',INSTR('en_UK','_')+1) country FROM dual
/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Here is a simple way:

select distinct left(s_name, 2)
from t

This assumes the language name is the left two characters.

Upvotes: 0

Related Questions