Getting the First Word of a String with MySQL

November 10th, 2010 - Posted by Steve Marks to MySQL, Web Development.

I was recently assigned the task of getting the first word from a field in a MySQL table. In my case it was getting the first part of a customers postcode. Following a quick look through the MySQL reference manual I was guided towards the SUBSTRING_INDEX() function. By using this function I was able to select the first part of the string by using an SQL query like so:

SELECT SUBSTRING_INDEX(`postcode`, ' ', 1) AS postcodeOutward FROM `customers`

Here I am passing the function three parameters; the table column name in question, the delimiter (in our case a space is being used), and the count of delimiters from the start of the string at which to stop at.

Should you want to loop through or use this information in PHP you can then do something like so:

$query = "SELECT SUBSTRING_INDEX(`postcode`, ' ', 1) AS postcodeOutward FROM `customers`";
$result = mysql_query($query) or die(mysql_error());
while ($row=mysql_fetch_assoc($result)) {
	echo $row['postcodeOutward']."\n";
}
This entry was posted on Wednesday, November 10th, 2010 at 9:28 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (3)
  1. vendredi says:

    Good job Steve :) Thank you

  2. @24013 – I have amended the original post to demonstrate getting the result and looping through it using PHP

  3. 24013 says:

    how do you print the result using array and while loop?