1

I'd like to take this attribute value: "MAP_PAR_ID": "0160011000", and populate a new field with "16-11".

This works: concat(substr("MAP_PAR_ID",0,3), '-', substr("MAP_PAR_ID",5,3)) but i need to detect leading zeros and not include them

I've tried numerous variations of CASE ELSE statements but can't even detect leading zeros. Feeble attempt below:

CASE
    WHEN substr("MAP_PAR_ID", 0, 1) = '0' THEN substr("MAP_PAR_ID", 1, 2)
    ELSE  substr("MAP_PAR_ID", 0, 3)

    WHEN substr("MAP_PAR_ID", 5, 1) = '0' THEN substr("MAP_PAR_ID", 6, 2)
    ELSE substr("MAP_PAR_ID",5, 3)
END

Would the python console make things a little easier?

  • 1
    I suppose you can have only one ELSE statement in CASE WHEN clause. – SS_Rebelious Nov 2 '14 at 0:01
  • true, plus attributes aren't 0 indexed either. – geomajor56 Nov 2 '14 at 1:46
  • how about: tostring(toint('0001')) does that return the string '1' ? – John Nov 12 '14 at 15:01
5

You can use a regex replace for this:

 regexp_replace('0160011000', '0+([1-9]+)0+([1-9]+)0+', '\\1-\\2')
 regexp_replace("MAP_PAR_ID", '0+([1-9]+)0+([1-9]+)0+', '\\1-\\2')

The regex is broken down like this:

  • 0+ - Any number of zeros at the start
  • ([1-9]+) - Any number of values between 1 and 9. Capture into group 1
  • 0+ - Any number of zeros in the middle
  • ([1-9]+) - Any number of values between 1 and 9. Capture into group 2
  • 0+ - Any number of zeros at the end

The regex_replace function is defined like this:

regexp_replace(string,regex,after) 

So in the after section we use \\1-\\2 to add the values from group 1 and 2 into our new string.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.