Split text with regex in nodejs

user_8002025 0
Viewed 51

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 temporary table test2 (comment varchar);

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

Regex replace operation:

sqlText
.replace(/\s+create(\s+|global\s+|temporary\s+)table\s+/gi, `-split- CREATE $1 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 temporary 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 temporary 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?

javascript regex
2 Answers
  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 `))
                                                ☝️
user_145297790

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.

user_113298900
Related