MySQL query to extract first word from a field

Swanny_13658 0
Viewed 46345

I would like to run a query that returns the first word only from a particular field, this field has multiple words separated by spaces, I assume I may need to carry out some regex work to accomplish this? I know how to do this using a few ways in PHP but this would best be carried out on the database side. Any ideas much appreciated. Thanks.

mysql sql php
5 Answers
evilpenguin_819460

Here you go :)

SELECT SUBSTRING_INDEX( `field` , ' ', 1 ) AS `field_first_word`
FROM `your_table`
user_819370 
select 
    substring(test_field, 1, instr(test_field, ' ')) 
from 
    test_table
Manrico_Corazzi_46900 
SELECT
  SUBSTR(field_name, 1, LOCATE(' ', field_name)) AS first_word
FROM
  table
user_765830

This piece of code may do the trick

select regexp_substr("Héctor-99 Eduvijes Uribe Curiel", '[[:graph:]]*')`

Output: Héctor-99

This is pretty similar from the others answers, but works in a different way, here you can find out more about it! https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Another optional could be:

select regexp_substr("Some name", '[a-z]+')

Ouput: Some

Inside the brackets you can put anything you want, learn some Regex you'll be able to do great things.

user_153780770
Related