Get housenumber, street and addition from text string

09 maart 2020

Get housenumber, street and addition from text string


With this SQL statement for MSSQL you can easily get housenumbers, house additions etc from a string. Mind you that this query is build to subtract dutch address strings. 
An address is like this "streetname 23A" OR "streetname 56 2hoog"

SELECT
    com_address
    ,CASE WHEN LEFT(REVERSE(street),1) = ',' THEN SUBSTRING(street,1,LEN(street)-1) ELSE street END AS street
    ,CASE WHEN PATINDEX('%[^0-9]%',housenumber) <> 0 THEN RTRIM(LEFT(housenumber,PATINDEX('%[^0-9]%',housenumber)-1)) ELSE housenumber END AS housenumber
    ,CASE WHEN PATINDEX('%[^0-9]%',housenumber) <> 0 THEN UPPER(LTRIM(SUBSTRING(housenumber,PATINDEX('%[^0-9]%',housenumber), 100))) ELSE '' END AS houseaddition
FROM (
    SELECT
        [com_address]
        ,CASE WHEN PATINDEX('% [0-9]%',com_address) <> 0 THEN LTRIM(LEFT(com_address,PATINDEX('% [0-9]%',com_address)-1)) ELSE com_address END AS street
        ,CASE WHEN PATINDEX('% [0-9]%',com_address) <> 0 THEN LTRIM(SUBSTRING(com_address,PATINDEX('% [0-9]%',com_address), LEN(com_address))) ELSE '' END AS housenumber
        ,CASE WHEN PATINDEX('% [0-9]%',com_address) <> 0 THEN ISNUMERIC(SUBSTRING(com_address,PATINDEX('% [0-9]%',com_address), LEN(com_address))) ELSE 0 END AS isValid
    FROM [company]
    WHERE [com_address] <> ''
) adr

 


Plaats jouw reactie