Substring an address using PSQL

Question

I have a list of addresses in a column:

Address

Tampa, FL. 33620

Zephyrhills, FL. 33539

(1,000 lines)

Is there a way to separate the city, state, and zip? I tried split_part but the function doesn't seem to work? I really need help on this! I am using PSQL. Thanks in advance!

You should tag with the actual database you are using. This will help to prevent users from investing time into answering the wrong question. — Tim Biegeleisen, Aug 07 '17 at 14:10
Whoops. Sorry, still learning. Literally joined this site because I have not received any training at my company and I'm trying to learn as much as possible. — tryingtolearn, Aug 07 '17 at 14:12
You're using a fairly rare database; I've never answered even a single question about Actian here. Maybe someone will be able to help you. — Tim Biegeleisen, Aug 07 '17 at 14:13
When you say "psql" do you mean "PostgreSQL"? If so set tag [tag:postgres] — Craig Ringer, Aug 07 '17 at 14:38
What you want to do is really hard in any database, assuming you have free-form addreses with the usual set of quirks and anomalies. Address processing is a whole discipline of its own. If you actually have uniform, well-formed "City, state. code" then it's a trivial regular expression match. — Craig Ringer, Aug 07 '17 at 14:39
For future reference, you may want to also try the official Actian product support forums for Actian PSQL (now known as Actian Zen Embedded Database) if you are not getting responses via Stack Overflow community forums... https://communities.actian.com/s/topic/0TO33000000LkvSGAS/actian-zen-psql — Hughesthe1st, Jan 11 '18 at 17:46

score 0 · Answer 1 · answered Mar 15 '22 at 13:34

Functions like SUBSTRING work normally in PSQL.

To split these addresses, assuming every city ends with a ,, and every state ends with a .

SELECT 
    SUBSTRING(address,1,LOCATE(',',address)-1) as City,
    SUBSTRING(address,LOCATE(',',address)+1,LOCATE('.',address)-LOCATE(',',address)) as State, 
    SUBSTRING(address,LOCATE('.',address)+2,40)  as Zip
FROM (
   SELECT 'Tampa, FL. 33620' as address
   UNION
   SELECT 'Zephyrhills, FL. 33539') x

output:

City	State	Zip
Tampa	FL.	33620
Zephyrhills	FL.	33539

Substring an address using PSQL

Address

1 Answers1