CODEWITHSUNDEEP
mysqlsqlregex
Claire
Claire

Reputation: 3773

How do I get first 1 or 2 characters of a string until a number appears in MYSQL?

I have a postcode field and want to only get the first letters that appear before a number appears. e.g. E11 would return E, HD4, would return HD.

Upvotes: 3

Views: 1642

Answers (2)

Anthony Scaife
Anthony Scaife

Reputation: 564

I hope this helps . . .

SELECT IF (postcode REGEXP "^[A-Z][A-Z]",LEFT(postcode,2),LEFT(postcode,1));

These examples demonstrate:

SELECT IF ("H5 7PL" REGEXP "^[A-Z][A-Z]",LEFT("H5 7PL",2),LEFT("H5 7PL",1));
-> "H"

SELECT IF ("HD5 7PL" REGEXP "^[A-Z][A-Z]",LEFT("HD5 7PL",2),LEFT("HD5 7PL",1));
-> "HD"

Upvotes: 2

cahen
cahen

Reputation: 16636

Use regex to find the position of the first number

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

and then use substring to get from 0 to this number

http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php

Upvotes: 2

Related Questions