I am trying to lowercase a backreferenced string but it wont work. :( I just want to lower the first character after root.
my_string = "<root>Here is July's best food</root>"
Correct one should be:
my_string = "<root>here is July's best food</root>"
Here is my query:
UPDATE my_table
SET my_string = regexp_replace(my_string, '<root>(.)', lower(E'<root>\\1'))
WHERE my_id = 1;
Using split_part() and lower()
select '<root>'||lower(split_part(split_part('<root>Here is July''s best food</root>',' ',1),'>',2)) || split_part('<root>Here is July''s best food</root>',split_part(split_part('<root>Here is July''s best food</root>',' ',1),'>',2),2)
Result:
"<root>here is July's best food</root>"
UPDATE my_table
SET my_string = '<root>'||lower(split_part(split_part(my_string,' ',1),'>',2)) || split_part(my_string,split_part(split_part(my_string,' ',1),'>',2),2)
WHERE my_id = 1;
following function is optional
You can make function like below
create or replace function lower_first_word(txt text,del text) returns text as
$$
select del||lower(split_part(split_part(txt ,' ',1),'>',2)) || split_part(txt ,split_part(split_part(txt ,' ',1),'>',2),2)
$$
language sql
and update
UPDATE my_table
SET my_string = lower_first_word('<root>Here is July''s best food</root>','<root>')
WHERE my_id = 1;
OR
Using regex and substring()
select lower(substring('<root>Here is July''s best food</root>',E'[A-Za-z<>]+'))||substring('<root>Here is July''s best food</root>',E'[^ ]* (.*)')
and the update statement is
UPDATE my_table
SET my_string = lower(substring('<root>Here is July''s best food</root>', E '[A-Za-z<>]+')) || substring('<root>Here is July''s best food</root>', E '[^ ]* (.*)')
WHERE my_id = 1;