Extracting number values from attribute string with mixed data

Question

In my attribute table, I have a column "Name" which contains data about type number and name of streets. For example: “SR 18 DI FIUGGI”. What I need is to create another column in which there will be only numeric data from column “Name”, so just "18".

I know how to do that in Excel, and that I can export/import data to do it there, but my guess is that you can do it directly in QGIS.

Also, one thing that can be useful to me is dividing columns by spaces. So there different columns for "SR", "18", "DI FIUGGI". Is this possible?

Answer 1

You are asking just the case that is given as an example of the regexp_substr() function:

regexp_substr("Name", '(\\d+)')

About your second question: You can create a Regular Expression to group each first, second and "third" word (the "third" word will be all that follows the second space), and put each group in a matrix, with a regexp_matches() funtion.

Then, extract the value for each position in the matrix by its index with the array_get() function.

---

For the first word until a space:

array_get(regexp_matches("Name",'^(\\w+)\\s(\\w+)\\s([\\s\\S]+)'),0)

For the word between first and second spaces:

array_get(regexp_matches("Name",'^(\\w+)\\s(\\w+)\\s([\\s\\S]+)'),1)

And for all the characters that follows the second space:

array_get(regexp_matches("Name",'^(\\w+)\\s(\\w+)\\s([\\s\\S]+)'),2)