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?
split the text into lines .split(/\n/)map each line check if it line begins with create table, if so change it as you want -split- CREATE TABLElet 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".
more simple with RegExp flag m
console.log(sqlText.replace(/^create\s+table\s+/gmi, `-split- CREATE TABLE `))
☝️
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.