Regular expression to replace only the first semi colon with a double quote

user_9087250 0
Viewed 172

There is a data dump that is semi colon separated.

How to load that data to the Postgres SQL database?

City;Latitude;Longitude
London;51.50;0.12
Paris;48.85;2.35

I thought of preparing an insert statements from this data using Atom text editor. But there comes another challenge the first semi-colon needs to be substituted with a single quote and a comma to make a valid insert statement.

DDL for the table:

create table cities (
    name varchar(100),
    Latitude float,
    Longitude float
);

The insert statement should look like this:

insert into cities values('London',51.50,0.12);
insert into cities values('Paris',48.85,2.35);

But I was able to generate only this by using atom text editor:

insert into cities values('London,51.50,0.12);
insert into cities values('Paris,48.85,2.35);

The above is achieved by replacing semi colon with comma. Using ^ to add insert into cities values(' in the beginning and $ to add ); in the add.

So the question is, what should be the regular expression to be used with atom text editor to replace the first occurrence of the semi colon?

atom-editor postgresql sql
2 Answers

This should work:

find: ^(.+);(.+);(.+) replace: insert into cities values('$1',$2,$3);

(I recommend this free resource as a good way to experiment with regexes: https://regex101.com/)

user_45905850

You do not need to pre-edit your input string, nor separate each entry to an individual insert; it can be done in one Insert statement. You have a string with known number of components and a separator so use the function regexp_split_to_array splitting by semi-colon (;). Then insert directly into the table referencing the elements and casting as needed. See Demo.

with loc( loc_str) as ( values('London;51.50;0.12'), ('Paris;48.85;2.35')) 
insert into cities(name, latitude, longitude) 
     select loc_part[1], loc_part[2]::float,loc_part[3]::float
       from ( select regexp_split_to_array(loc_str,';') loc_part from loc) l;

You could also use COPY to load a staging table from a source file then populate your table from it using the essentially the same as above. (also in demo)

user_76238560
Related