do not REGREPLACE string_agg were length is less than

ringus 0
Viewed 21

I am looping through a table and XXXXX out first names and surnames.

The below code works, but obviously things go badly wrong when an Initial is used. I could do first name and surname separately and use a length clause.

But I was wondering if there is away to ignore first names and surnames where they are less than 2 in length?

create table reg_test(id integer, note_text text)

insert into reg_test values (1,'sjdfhsdjh sdfsdf ksdfksf John dsfsfgsdfhj Smith dsfsfsdf');

update reg_test
SET note_text = regexp_replace(note_text, 'J|Smith' , 'XXXXX', 'ig')
where id = 1;
regexp-replace sql postgresql regex
1 Answers

In the regex, you can mandate a range of characters. For example if you insist on the string to be at between 3 and 5 characters you would use {3,5}. Both the lower and upper bound are optional.

In your example below, to eliminate 2 character initials such as "JS", you could use J[A-Z]{2,} (J followed by 2 or more letters), meaning it has to be at least 3.

insert into reg_test values
(3,'sjdfhsdjh JS sdfsdf ksdfksf John dsfsfgsdfhj Smith dsfsfsdf');

Update the regex:

update reg_test
SET note_text = regexp_replace(note_text, 'J[A-Z]{2,}|Smith' , 'XXXXX', 'ig')
where id = 3

You'll see it updated "John" but not "JS."

Hambone0
Related