SQL Query Syntax Splitter

Question

How to split these query from a single string into an array of single queries?

example:

"SELECT * FROM table1; SELECT * FROM table2;"

into

[
"SELECT * FROM table1;",
"SELECT * FROM table2;"
]

Well, I can't use javascript.split(";") because there can be semi-colon in the query as values. Like

"SELECT * FROM table 1 WHERE col LIKE ';'"

thanks.

If you send SQL from client to your server to be executed (even worse if through JavaScript)...change your address, obfuscate your name and pretend you're a photographer. It's _SQL injection made easy_. From client you have to collect/send parameters, query must be built server-side. — Adriano Repetti, Oct 01 '14 at 07:28
Thanks guys. I'm actually making a sql terminal here. so I need the queries from html. — kenicky, Oct 01 '14 at 08:49
If you're doing a SQL terminal then use a simple regex: `subject.match(/[>#.[{](?:"[^"]*"|[^">#.[{])+/g);` as described [here](http://stackoverflow.com/questions/22503197/regex-split-string-on-specific-chars-outside-quotes). — Adriano Repetti, Oct 02 '14 at 09:59

score -1 · Answer 1 · answered Oct 01 '14 at 07:27

-1

You can split using the split string "; SELECT" and add SELECT at the begining of the new strings (except the first one)

answered Oct 01 '14 at 07:27

slewden

9
1

**Any** text may be contained in a SQL string. Simply string splitting isn't viable here (unless using a much more complex regex that considers also string delimiters) – Adriano Repetti Oct 01 '14 at 07:29
Is there any libraries for this? – kenicky Oct 01 '14 at 08:49
can't do this. It's not always "Select" it could be Insert, Update, etc. – kenicky Oct 01 '14 at 12:22

score -2 · Answer 2 · answered Oct 01 '14 at 07:34

-2

Use javascript.split("; ") because this is a pattern each line ends with '; ' if this is not possible in java you can create a split string function in sql

answered Oct 01 '14 at 07:34

SubqueryCrunch

1,325
11
17

1

`"SELECT * FROM table 1 WHERE col LIKE ';'"` this king of query will return an error in this kind of solution. – kenicky Oct 01 '14 at 08:50

SubqueryCrunch · Answer 3 · 2014-10-01T10:06:29.950

-2

Here you can split it like that

DECLARE @Text NVARCHAR(MAX)
SELECT @Text = '"SELECT * FROM table1; SELECT * FROM table1;","SELECT * FROM table1; SELECT * FROM table2;"'
SELECT @Text = REPLACE(@Text,'"','')
SELECT @Text = 'SELECT ('''+ REPLACE(@Text,';','''); SELECT(''') + ''')'
SELECT @Text = REPLACE(@Text,'; SELECT('''')','')
PRINT @Text
EXEC (@Text)

If you want to print it

DECLARE @Text NVARCHAR(MAX)
SELECT @Text = '"SELECT * FROM table1; SELECT * FROM table1;","SELECT * FROM table1; SELECT * FROM table2;"'
SELECT @Text = REPLACE(@Text,'"','')
SELECT @Text = 'PRINT ('''+ REPLACE(@Text,';','''); PRINT(''') + ''')'
SELECT @Text = REPLACE(REPLACE(@Text,' SELECT','SELECT'),',SELECT','SELECT')
EXEC (@Text)

edited Oct 01 '14 at 10:06

answered Oct 01 '14 at 10:00

SubqueryCrunch

1,325
11
17

what are these called? – kenicky Oct 01 '14 at 12:21
This is dynamic sql with some string formatting im not sure if my answer is directly what you want but i think you can achieve it in this way :) – SubqueryCrunch Oct 01 '14 at 13:00

SQL Query Syntax Splitter

3 Answers3