Getting first part from the Postcode?

Question

Using MySQL how to grab first part of the data.postcode field?

For example:

M40 1JY
M8 8NW

I want to display like this:

M40
M8

Answer 1

select substring(postcode, 1, locate (' ', postcode) - 1) from data;

locate (' ', postcode) returns the position of the space (), the first input, out of the second input (your column), you don't want to display the space so subtract 1 from this number.

substring(data, start, length) takes the substring of the data (your column) from the number of start, you want to start at the beginning, so this is 1, and displays the number of letters you enter in length, this is the result of locate.

Answer 2

The SUBSTRING_INDEX(str,delim,count) function is what you need. This function return the substring from string str before count occurrences of the delimiter delim. You can go the this link for more details. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

For your problem,

SELECT SUBSTRING_INDEX(postcode, ' ', 1) from YourTable; 

will return

M40
M8