group on words in a varchar attribute
postgresql add tag
Anonymous 2230 
I have a question about this fiddle:

<>https://dbfiddle.uk?rdbms=postgres_14&fiddle=49a34fe48d596eee37344177d0fef85f

How do I group the objects in groups where all objects has the same two first words in "tmaname"? I.e. all 'ARAD %' shall be in one group etc.

I tried this:
SELECT substring(t1."tmaname" from '^\w\s\w') AS first_key_words
--, ST_MULTI(ST_UNION(ST_SnapToGrid(t1.the_geom, 0.000001))) AS geom
  FROM "nisse" AS t1
 GROUP BY substring(t1."tmaname" from '^\w\s\w')
 
 But the query only returns first_key_words = null.
Top Answer
Andriy M 
If the words in your case are always going to be separated by the same character (like a space, for instance), you could try using the [`split_part()`][1] function instead of regular expressions.

<>https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f5c1c74ae9902284f872743555e7d96f&hide=1

[1]: https://www.postgresql.org/docs/current/functions-string.html#id-1.5.8.10.7.2.2.31.1.1.1
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.