I try to find sql script in a file to split in nodejs. Before split to text I add like -split- seperator with regex replace to begining of the sql script as the below:

SQL file:

/* this is a comment for create table */
--this is another comment for create table

create table test1 (comment varchar);
create table test2 (comment varchar);

insert into text1 values('this is a comment for create table ')

Regex replace operation:

sqlText
.replace(/\s create\s table\s /gi, `-split- CREATE TABLE `)

Expected output:

/* this is a comment for create table */
--this is another comment for create table

-split- CREATE TABLE test1 (comment varchar);
-split- CREATE TABLE test2 (comment varchar);

insert into text1 values('this is a comment for create table ')

But i get:

/* this is a comment for -split- CREATE TABLE */
--this is another comment for -split- CREATE TABLE

-split- CREATE TABLE test1 (comment varchar);
-split- CREATE TABLE test2 (comment varchar);

insert into text1 values('this is a comment for -split- CREATE TABLE ')

How can I exclude the query sentences in the comment line and quotes?

CodePudding user response：

1. First split the text into lines .split(/\n/)
2. Loop with map each line check if it line begins with create table, if so change it as you want -split- CREATE TABLE

let sqlText = `/* this is a comment for create table */
--this is another comment for create table

create table test1 (comment varchar);
create table test2 (comment varchar);

insert into text1 values('this is a comment for create table ')
`;
console.log(sqlText.split(/\n/).map(line=>line.replace(/^create\s table\s /gi, `-split- CREATE TABLE `)).join('\n'))

Notice: the ^ means "starts with".

UPDATE

more simple with RegExp flag m

console.log(sqlText.replace(/^create\s table\s /gmi, `-split- CREATE TABLE `))
                                                ☝️

CodePudding user response：

Use

const str = `/* this is a comment for create table */
--this is another comment for create table

create table test1 (comment varchar);
create table test2 (comment varchar);

insert into text1 values('this is a comment for create table ')`
console.log(str.replace(/(\/\*[^]*?\*\/|^\s*--.*|'[^\\']*(?:\\[^][^\\']*)*'|"[^\\"]*(?:\\[^][^\\"]*)*")|[^\S\n\r]*create\s table\s /gmi, (_, x) => x || `-split- CREATE TABLE `))

We skip multiline comments (\/\*[^]*?\*\/), single line comments (^\s*--.*), single quote literals ('[^\\']*(?:\\[^][^\\']*)*') and double quote literals.

The [^\S\n\r]*create\s table\s regex part finds matches outside of afore-mentioned patterns.