CODEWITHSUNDEEP
phpmysqllogic
AgeDeO
AgeDeO

Reputation: 3157

Split housenumber from Addressfield

I would like to split a single address field into a street and housenumber field.

This is some example data:

Examplestreet 1
Examplestreet 1A
Examplestreet 1 B
The Examplest street 2
1st Example street 3A
1st Example street 13 A

Now, I was thinking that I start from the right and look for the first number I encounter, then keep going until the first space is encoutered and split there.

You would get something like this:

Example:                           1st Example street 13 A
Start from the right:              A
Find the first number:             A 3
Keep going until the first space:  A 31
Split here:                        1st Example Street    |     13A

I would like to get this working in mySQL alone, but using PHP is also possible.

When you know a better approach for this, I would like to know to.

I started looking at SUBSTRING_INDEX, but that didn't do the trick.

Frankly I haven't got a clue where to start.

Upvotes: 1

Views: 171

Answers (1)

user4035
user4035

Reputation: 23749

If the house number is always at the end of the string, starting from the digit, we can use a regexp:

<?php
$names = array(
    'Examplestreet 1',
    'Examplestreet 1A',
    'Examplestreet 1 B',
    'The Examplest street 2',
    '1st Example street 3A',
    '1st Example street 13 A ',
    );

foreach($names as $value)
{
    $matches = array();
    if (preg_match('/.*\s(\d.*)/', $value, $matches))
    {
        print $matches[1]."\n";
    }
}

Output:

1
1A
1 B
2
3A
13 A

Upvotes: 1

Related Questions