QGIS: String formatting when creating a virtual field

Question

I would like to join US census population estimates with county boundaries.
The population estimates layer (co-est2019-alldata) contains STATE and COUNTY fields with state and county FIPS codes, like this:

STATE
COUNTY

36
51

1
15

The county boundary (cb_2018_us_county_5m) layer contains the same values but with slightly different formatting and additionally a GEOID string field that combines the two:

STATEFP
COUNTYFP
GEOID

36
051
36051

01
015
01015

QGIS doesn't support joining on multiple fields, but a common workaround is to create a new virtual field that combines the target attributes. I want to create a virtual field in the population layer that matches the GEOID field in the county boundary layer.
This would be simple if there were a function equivalent to sprintf, because then I could just write:
sprintf('%02s%03s', "STATE", "COUNTY")

...but as far as I can tell, no such function exists. What's the best way to handle this?

J. Monticolo · Answer

Try this one :
lpad(to_string("STATE"), 2, '0') || lpad(to_string("COUNTY"), 3, '0')

returns
if "STATE" = 1 and "COUNTY" = 15 : 01015
if "STATE" = 22 and "COUNTY" = 5 : 22005
From the documentation:

lpad(string, width, fill) (doc) :

Returns a string padded on the left to the specified width, using a fill character. If the target width is smaller than the string’s length, the string is truncated.

to_string(number) (doc) : (because I wasn't sure that your fields were characters)

Converts a number to string.

|| : concatenate two strings

QGIS: String formatting when creating a virtual field

One Answer

Add your own answers!

Ask a Question