Helpme please query about postgres

Question

I need to extract a number of characters from one column to another and insert it into another column

number_operation	communication	test
	B003-00000005
	FCI-0023
	0097
	F004-00000021-220063	-220063
	F003-00000001-196322906	-196322906
	F004-00000125-0176218	-0176218
	85373386
	F004-00000064
	F004-00000044
	F003-00000023
	F003-00000023
	F004-00000061
	0007-0005006
	F003-00000019-8332	-8332
	B003-00000002
	F004-00000135-012040	-012040

I want what is in the test column to be inserted in the number_operation column, and I made this query

select number_operation,communication,
substr(communication,14) as test from account_payment

Answer 1

You could use SUBSTRING along with a regex pattern. Assuming you want to select this new column, use:

SELECT number_operation, communication,
       SUBSTRING(communication FROM '[^-]+-[^-]+-(.*)') AS test
FROM yourTable;

If you instead want to update your table, and the new test column has already been created, then use:

UPDATE yourTable
SET test = SUBSTRING(communication FROM '[^-]+-[^-]+-(.*)')
WHERE communication LIKE '%-%-%';