Turning one column to multiple columns in Attribute Table?

Question

This is kind of a two-part question to see which route would be more efficient.

I'm trying to display a label in a shortened version, but the info is all in one column:

Township 1 South Range 20 West

to

T01S-R20W

I tried substr() expression, but since the numbering (for both numbers in the string) vary from 1-29 I cannot use a long substr() expression that joins them together. It moves the start/stop position.

So, I was thinking to somehow do what MS Excel does with "text to columns", but in QGIS. Is there any way to do that? Or, is there an easier way to do what I'm looking for?

• geozelot: you could use string_to_array("column", ' ') (note the space character as split mark) and go from there (access via common array notation, array[<index>])?
• geozelot: nevermind, with your edit I realize that you want to abbreviate that string.

Answer 1

Working with @ThingumaBob's string_to_array suggestion:

 with_variable('a',string_to_array('Township 1 South Range 20 West',' '),
 concat(left(@a[0],1),
 right('0'||@a[1],2),
 left(@a[2],1),
 '-',
 left(@a[3],1),
 right('0'||@a[4],2),
 left(@a[5],1) )
 )

You can split the string into an array and then get the first letter of each element and join them together. For the numbers instead of getting the first character you add a zero '0' to the start and the retrieve the two characters on the right. Just replace the hard coded 'Township 1 South Range 20 West' with the name of the column containing the data.

This expression will work in QGIS 3 but not in QGIS 2