4

I have a large data set that contains a time range in the format 00:00-00:00 and 1-6 three-day abbreviations of days of the week (MON, TUE, WED, etc.). I managed to get all the time ranges and day abbreviations to be in the same format, but in some strings, the time is before the days of the week and in others it comes after, e.g.:

08:00-17:00 MON TUE WED THU FRI

MON THU 11:30-13:00

TUE 08:30-10:00

I want to move the time range to the beginning of all the strings. I've gotten as far as filtering the features that end in numbers...

regexp_match("SIGNDESC1",'(\\d)$') 

... and selecting the last 11 characters of the string:

right("SIGNDESC1", 11)

... but I am stumped about moving them to the beginning of the string.

2
  • Technically this is off topic - see stackoverflow.com/questions/14007545/… for the answer in pure python, which should work in QGIS too
    – Ian Turton
    Commented Apr 24, 2017 at 7:44
  • Thanks for the link. This is very basic, but I'm assuming that you use the Python Console in the Plugins menu to use Python in QGIS...?
    – S. Signs
    Commented Apr 24, 2017 at 15:48

2 Answers 2

1

Another option: Use regular expressions to look for the space between 1) the weekdays part and 2) the time range part. Then split up the string at this position. Use this expression (see below for explanation):

array_to_string(
    array_sort(
        with_variable(
            'len',
            regexp_match(
                timestring,
                '[A-Z] \\d|\\d [A-Z]' -- find space
            ),
            array (
                substr (timestring, 0,@len), -- first element
                substr (timestring, @len+2) -- second element
            )
        )
    ),
    delimiter:=' '
)
  1. Use regexp_match() to find the pattern:

    • character - space - digit using [A-Z] \\d
    • digit - space - character using \\d [A-Z]

    Connect both with logical operator "or" | to find one or the other pattern. The position of the space found like this is where weekdays and time range are separated.

  2. Now we can use this position to get the part of the string before and after it with substr()

  3. Create an array with the characters up to this split point (1st element) and the characters after it (2nd element). Sort the array to get the time at the beginning and convert the array back to a string.

1

Solution: Regular expressions

Use twice regexp_matches(), once to get the time range part, the second time to get the weekdays part, then concatenate them (replace timestring with your input fieldname):

trim (regexp_matches (timestring, '([\\d:-]{1,})')[0])
|| ' ' || 
trim (regexp_matches (timestring, '([A-Z ]{1,})')[0]) 

See screenshot below.

Explanation of the regular expression part:

  • ' regex part starts/ends with single quote
  • ( and ) encloses captering group
  • [\\d:-] a digit or a : or a - character
  • {1,} repeated one or more times
  • [A-Z ] a character from A to Z (any uppercase letter) or a space

Explanation for the rest of the expression:

  • regexp_matches() creates an array with the captured groups (regexp part in brackets); has two arguments: input string and regexp part
  • [0] (index operator) get the first element (index 0) of the array, here: the group captured by the regular expression
  • timestring input string: the name of the field containing the string
  • trim delete leading and trailing whitespaces from the resulting string
  • || (pipe operators): to concatenate the different strings

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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