Flexible Web Services Info Tech Support

This post is for PostgreSQL

I had a field in a database that consisted of city and state in the same field. It's a lot easier to create a single field out of two fields than it is constantly have to split that field up. For example, If you have a field called city and a field called state, you could easily make a citystate field that could be represented thusly: city+', '+state. The other way around requires finding the comma, removing the leading space, handling the exception that there is no comma or that there is no state or that there is only a state and no city (and those exceptions may have to be looked at for making a composite field like citystate, but it is easier to not print a comma if one fields [city or state] is missing).

What I decided to do was create an array out of the already composite field and split it on the comma. In Postgres, that function is string_to_array. However, string_to_array(citystate,',') produced an array that had double quotes around the state, in order to preserve the leading space. So I reverted to this: string_to_array(replace(citystate,' ',''),',') which gave me a nice 1 dimensional array with 2 elements (city in element 1 and state in element 2), but extracting an element from a function call is quite another problem. Since the function is inside of a select query there is no feature which allows us to make temporary assignments so we could assign the return value of a function to a temporary variable in order to do more operations on that variable. Of course, this is what stored procedures (or stored functions for Postgre) are for. We could easily pass this into a stored function and get the value back out, but is it possible to get the element out of the select statement without using a stored function?

Our table has 2 elements:

CREATE TABLE capitals (
id serial,
citystate character varying
);

INSERT into capitals (citystate) values ('LOS ANGELES, CA');

SELECT string_to_array(replace(citystate,' ', ''),',') as data from capitals;

renders:

[LOS ANGELES,CA]

Dang it, I stepped in it big time.

I was hoping the get the element out of the select statement by appending the element index on the end of the function, which I thought wouldn't work... and it didn't, like so:

SELECT string_to_array(replace(citystate,' ', ''),',')[1] as data from capitals;

Which returns an error. I just read on this post here that surrounding the entire expression would return the element like so:

SELECT (string_to_array(replace(citystate,' ', ''),','))[1] as data from capitals;

And this worked. This is what I ended up doing which made me decide maybe a stored function would be a better answer, but I got this from the Postgre documentation and then convoluted it to fit my situation, here it is for the dart board:

SELECT (SELECT f1[1] as city from (SELECT string_to_array(replace(citystate,' ',''),',') as f1) as ss) from capitals;

It worked of course, but I didn't want to do it again for the state.  I got the sample I followed from this site. As all the functions and examples always returned arrays, I needed one that returned the value of an element and there was only one on that page that did it.