2

I have metadata in a postgresql table that I need to split. So far I have been attempting to use

Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | etc...); 

Out of a table formatted like so:

QGC 10''
QGC 10''
QGC 10''
QGC 10''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 10''

I need only to extract the integers and would like to preserve the acronyms if possible. I have attempted to use the split substring with regex function but haven't found much success, although this is primarily due to my lack of understanding of how to pass substrings inside of queries when dealing with a table. If someone could explain the appropriate syntax in order to perform this task they would help me greatly. Thanks in advance.

Thanks Martin, You are right - I placed this in the wrong section, thanks for your input. Also, you are correct in assuming that the quotes are holdovers from the database and I have corrected them to more accurately represent the data. How would I go about having the topic moved as not to darken the door of gis.stackexchange?

  • Is your compound attribute simply of the form "acronym codenum" or is it of the form "acronym codenum''" (with 2 extra single quotes)? Also, is the fact that it is metadata relevant? It's just string data isn't it? Please edit the question (via button between share and flag) for clarity, thanks. – Martin F Mar 25 '14 at 20:42
  • Good, you've edited the question, but then the comments should have come done here. You soon get the hang of things. :-) To move a misplaced Q, needs anyone or you or I to "flag" it so, then wait for a "moderator" to do the actual move. For now, let's just see if we can get your SQL problem sorted. If the whole thing moves, before, during or after, i think no harm's done. Anyway, we're a colorful bunch, so don't worry about darkened doors. – Martin F Mar 26 '14 at 3:56
0

After looking at split_part() from PostgreSQL String Functions, and assuming that column is the name of the "compound attribute" that is of the form "acronym codenum", then I'd say you should try something like this

SELECT 
  split_part (column, ' ', 1) AS acronym,
  split_part (column, ' ', 2) AS codenum
FROM table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.