Menu
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.
695
SELECT SUBSTRING_INDEX(yourColumnName,' ',1) as anyVariableName from yourTableName; In the above query, if you use -1 in place of 1 then you will get the last word.
To extract first word from a field, use in-built SUBSTRING_INDEX () function. The syntax is as follows − In the above query, if you use -1 in place of 1 then you will get the last word.
What's the best way to extract the first word of a string in sql server query? SELECT CASE CHARINDEX (' ', @Foo, 1) WHEN 0 THEN @Foo -- empty or single word ELSE SUBSTRING (@Foo, 1, CHARINDEX (' ', @Foo, 1) - 1) -- multi-word END CREATE FUNCTION [dbo].
Extract the second and successive words from the indicated field: SELECT SUBSTRING (field1, CHARINDEX (' ', field1)+1, LEN (field1)-CHARINDEX (' ', field1)) FROM table1; A slight tweak to the function returns the next word from a start point in the entry CREATE FUNCTION [dbo].
MySQL SUBSTRING() Function The first position in string is 1 If start is a positive number, the SUBSTRING() function starts from the beginning of the string If start is a negative number, the SUBSTRING() function starts from the end of the string
SUBSTRING_INDEX: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
SELECT SUBSTRING_INDEX(`name`, ' ', 1); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
