Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your sample output is confusing. The first line implies that you want to break the street address down to its individual components, but in the rest of the lines it's all bunched together. I would expect the desired result to be either:</p> <pre><code>"123 SUNNYSIDE AVENUE", "BROOKLYN" "59 MAIDEN LANE", "MANHATTAN" "59 MAIDEN LANE", "MANHATTAN" "39-076 46 STREET", "SUNNYSIDE" "39-076 46 STREET", "SUNNYSIDE" "59 MAIDEN LANE", "MANHATTAN" </code></pre> <p>...or:</p> <pre><code>"123", "SUNNYSIDE", "AVENUE", "BROOKLYN" "59", "MAIDEN", "LANE", "MANHATTAN" "59", "MAIDEN", "LANE", "MANHATTAN" "39-076", "46", "STREET", "SUNNYSIDE" "39-076", "46", "STREET", "SUNNYSIDE" "59", "MAIDEN", "LANE", "MANHATTAN" </code></pre> <p>In either case, I would start by matching it with this regex:</p> <pre><code>^(\S+(?:\s+\S+)*)\s+(MANHATTAN|BROOKLYN|SUNNYSIDE) </code></pre> <p>The first group is greedy, so it will initially consume all but the last word of the address string. If the last word is not a city name (that is, it doesn't match the <code>(MANHATTAN|BROOKLYN|SUNNYSIDE)</code> group), the first group "gives up" one word at a time until the second group <em>does</em> match. </p> <p>Assuming the string actually contains a city name, and the name is included in the second group's subexpression, it will be captured in group #2. Group #1 will contain the whole street address; if you want it broken up as shown above, you can split it on whitespace.</p> <p><strong>EDIT:</strong> Here's some sample code to demonstrate. Note especially the use of <code>find()</code> instead of <code>matches()</code>. The behavior of Java's <code>matches()</code> method surprises many people, and it occurred to that it might be part of the problem here. In a nutshell, <code>find()</code> is why I had to add <code>^</code> the beginning of the regex, and why I <em>didn't</em> have to add <code>.*</code> to the end. ;)</p> <pre><code>String[] ss = { "123 SUNNYSIDE AVENUE BROOKLYN", "59 MAIDEN LANE MANHATTAN", "59 MAIDEN LANE MANHATTAN 10038", "39-076 46 STREET SUNNYSIDE", "39-076 46 STREET SUNNYSIDE 11104", "59 MAIDEN LANE MANHATTAN NY USA" }; Pattern p = Pattern.compile("^(\\S+(?:\\s+\\S+)*)\\s+(MANHATTAN|BROOKLYN|SUNNYSIDE)"); Matcher m = p.matcher(""); for (String s : ss) { if (m.reset(s).find()) { System.out.printf("%naddr: '%s'%ncity: '%s'%n", m.group(1), m.group(2)); } } </code></pre> <p>output:</p> <pre><code>addr: '123 SUNNYSIDE AVENUE' city: 'BROOKLYN' addr: '59 MAIDEN LANE' city: 'MANHATTAN' addr: '59 MAIDEN LANE' city: 'MANHATTAN' addr: '39-076 46 STREET' city: 'SUNNYSIDE' addr: '39-076 46 STREET' city: 'SUNNYSIDE' addr: '59 MAIDEN LANE' city: 'MANHATTAN' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POparse string with potentially 2 occurrences of the same string
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAlan Moore
    1. USAlan Moore
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POparse string with potentially 2 occurrences of the same string
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou're right, I don't care about separating out the beginning parts, I just want the "city" and the rest. This is working for the problem as I have described it.
      singulars
      1. PO
      1. USSarah Haskins
    2. CO+1 - should work most of the time! The only potential snag is when/if someone puts something like `123 SUNNYSIDE AVENUE` (i.e. without a city) - it will match with `123` and `SUNNYSIDE`, then drop the `AVENUE` part - changing the `*` to a `+` should fix this, by requiring 2 instances of "non-whitespace followed by whitespace" (presumably a number and name or at least two words) at the risk of excluding things like `MAYOR MANHATTAN NY` - not sure how likely this is to be an issue...?
      singulars
      1. PO
      1. USCode Jockey
    3. COAhh, but what about `123 LOWER SUNNYSIDE AVENUE`? :P But seriously, if you can assume the city name is present, everything else will sort itself out. And if you *can't* assume that, it becomes a completely different question--one I probably won't try to answer. ;)
      singulars
      1. PO
      1. USAlan Moore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload