Regex'pert' question remove SQL exported column

[compumatter]

I’ve created an exported SQL file like this and before I can import into another DB I need to remove the first column of data and its comma
For instance in the first row below that would be : 1,
I am grateful if anyone with the expertise can suggest the right replace code for turning x, into blank entry.

INSERT INTO cm_def_options (categ_id, model_id, parent_group, option_group, option_name, option_value, option_description, option_orderby) VALUES
(1, 70, ‘0’, ‘’, ‘root-vessels’, ‘#’, ‘’, 0),
(2, 70, ‘1’, ‘’, ‘Boat Type’, ‘boat-type’, ‘’, 1),
(5, 70, ‘2’, ‘’, ‘Sail’, ‘sail’, ‘’, 0),
(6, 70, ‘5’, ‘’, ‘Antique and Classic (Sail)’, ‘sail-antique’, ‘’, 1),
(7, 70, ‘5’, ‘’, ‘Barge (Sail)’, ‘sail-barge’, ‘’, 2),
(8, 70, ‘5’, ‘’, ‘Beach Catamaran’, ‘sail-beachcat’, ‘’, 3),
(9, 70, ‘5’, ‘’, ‘Catamaran’, ‘sail-catamaran’, ‘’, 4),
( 10, 70, ‘5’, ‘’, ‘Center Cockpit’, ‘sail-centercockpit’, ‘’, 5),
( 11, 70, ‘5’, ‘’, ‘Commercial (Sail)’, ‘sail-commercial’, ‘’, 6),
( 12, 70, ‘5’, ‘’, ‘Cruiser (Sail)’, ‘sail-cruiser’, ‘’, 7),

Thank you.

Jay

[Terry R]

@compumatter, I’m not entirely sure whether you mean
“I need to remove the first column of data and its comma” or
“suggest the right replace code for turning x, into blank entry”.

The first suggests deleting the number and it’s comma, the second suggests just replacing the number with a blank space.

I’ll go with the first idea, completely removing the number and comma.
So:
Find what: ^(\()(\h*\d+,\h)
Replace with: \1
In the Replace window, have “regular expression” selected as well as “wrap around”. Clicking the “replace all” button will complete the changes in the entire file.

I’m a bit concerned that the first 9 rows have the number against the (, then there is a space after the ( in the next 3 rows. Hence my regex has the \h* included. I’ve also included a following space so the “70” appears directly behind the (. This could be removed if it’s not essential the “70” is directly behind the (.

This is a fairly easy regex to build, I suggest you read our FAQ, in particular “FAQ Desk: Where to find REGEX documentation ?” it contains lots of links to very good information, it will help get you started learning about regex.

To start you off we have (description):
^ means the start of a line
( is the start of a group
( the \ is a delimiter, because the ( is a special character, so this means literally the (
) is the end of the group, so anything contained within this is captured
( is the start of another group
\h* means as many spaces as exists including none
\d+ means at 1 or more digits
, is of course the comma, literally
\h means 1 space which MUST exist. This could be a \h* if possibly no spaces exist in this position on some rows
) closing of the group

\1 means the 1st captured group is returned. The 2nd captured group is what we want removed, hence it is not added to the replace field and is effectively deleted.

Hope this helps

Terry