CODEWITHSUNDEEP
sqlpostgresql
Jack
Jack

Reputation: 115

Removing the first or last delimeter in a string SQL

create table addresses (address varchar(50));
insert into addresses(address) values
('123 Pillage Road, EN45EX'),
('123 Pill,age Road, EN45EX'),
('123 Pillage Road, EN4,5EX');

and i'm trying to remove the first , delimiter and the last by:

update addresses 
set address = 
        case address
                when strpos(address, ',') < 17 then regexp_replace(address, ',','')
                when strpos(address, ',') > 17 then REVERSE(regexp_replace(REVERSE(address), ',',''))
        end
where char_length(address) - char_Length(replace(address,',','')) > 1

This gives a

ERROR:  operator does not exist: character varying = boolean
LINE 4:     when strpos(address, ',') < 17 then regexp_replace(addre...
            ^

So Im finding the middle delimiter location (17) and trying to do a specific replace function on the delimiters found before and after. Where is the error coming from?

Upvotes: 0

Views: 63

Answers (2)

arutar
arutar

Reputation: 1093

I used positive look back (?<=\w) and positive look ahead (?=\w). thus, if a comma is between two characters or numbers, then it is replaced with an empty space

select regexp_replace(address, '(?<=\w),(?=\w)', '', 'g') from addresses;

results

      regexp_replace
--------------------------
 123 Pillage Road, EN45EX
 123 Pillage Road, EN45EX
 123 Pillage Road, EN45EX

and query with update

update addresses set address=regexp_replace(address, '(?<=\w),(?=\w)', '', 'g');

Upvotes: 1

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

Remove the address identifier from CASE:

update addresses 
set address = 
        case 
                when strpos(address, ',') < 17 then regexp_replace(address, ',','')
                when strpos(address, ',') > 17 then REVERSE(regexp_replace(REVERSE(address), ',',''))
        end
where char_length(address) - char_Length(replace(address,',','')) > 1

Upvotes: 1

Related Questions