0

My layer contains an attribute with a size of 11 characters (of type text) whose variable values are of this type: '12345AB1234'.

Some variable values include a space at the 6th position, like this: '54231 C4356' I'm looking to replace those spaces with a zero ('0').

The following command is used to list the 6th characters of the variable values:

select park_index, substring(compar from 6 for 1)
from my table;

From there, I tried (without success) the following 2 commands:

UPDATE my table
SET compar = REPLACE (compar, substring(compar from 6 for 1),'0')
WHERE substring(compar from 6 for 1) = '';

    
UPDATE my table
SET compar = TRANSLATE (compar, substring(compar from 6 for 1),'0') 
WHERE substring(compar from 6 for 1) = '\U+20';
4
  • 1
    why not simply use replace(compar, ' ', '0') - could there be other spaces in the value that should not be replaced?
    – user1822
    Nov 4, 2022 at 7:43
  • @a_horse_with_no_name Thank you, but it doesn't work (the update request doesn't return an error, but no changes are made)
    – fcka
    Nov 4, 2022 at 8:34
  • 1
    Then your WHERE clause seems to not find anything. Did you try = ' ' instead? (Note ' ' not '')
    – user1822
    Nov 4, 2022 at 9:15
  • @a_horse_with_no_name as much for me, it works, Thank you
    – fcka
    Nov 4, 2022 at 17:19

1 Answer 1

1

You can use regex for both searching and updating. Here is the expression which replaces all (one or more) 'space' ( ) characters with a (only one) zero (0).

regexp_replace('12345 AB1234', '([ ])+', '0')

The update expression can be like this;

update my_table set 
    compar = regexp_replace(compar, '([ ])+', '0')
  where
    compar ~ '([ ])+';

Here is a sample fiddle

2
  • Wow ! Thank You !
    – fcka
    Nov 4, 2022 at 20:31
  • 1
    @fcka please accept the answer if its ok.
    – Sahap Asci
    Nov 4, 2022 at 21:18

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.