Extract sub Strings From a String

Question

I have a DB script that I have read from a file and take that script in a string variable. The file contains multiple DB scripts to execute. Now i want to break that large string into sub-strings as my string found GO keyword.

I am using

myString.split("Go");

--Note: "Go" keyword is not case sensitive for executing a script it can be "go", "Go", "GO" or anything

but it is not working for me because there are some tables or database names that contains GO and the script split there as well.

here is my script:

---- Database New_Db_Gomsle
IF NOT EXISTS (SELECT * FROM user_table_gomsle WHERE USER id = 1124)
BEGIN
ALTER TABLE user_table_gomsle
ADD user_img    varchar(MAX)
END
Go

 --- Database Angolifie_Db
IF NOT EXISTS (SELECT * FROM user_table_Angolifie WHERE USER id = 1124)
BEGIN
ALTER TABLE user_table_gomsle
ADD user_img varchar(MAX)
END
GO                                                                                                                                                                  


ALTER TABLE gotham_Accessories
ALTER COLUMN stationary_count   INT
go

Like there Exits 'Go' keywords in script Comments, table names, database names.

I am Expecting result like

string[] myQueryArray = new string[10];
myQueryArray[0] = "---- Database New_Db_Gomsle

IF NOT EXISTS (SELECT * FROM user_table_gomsle WHERE USER id = 1124)
BEGIN
ALTER TABLE user_table_gomsle
ADD user_img    varchar(MAX)
END
Go"

myQueryArray[1] = " --- Database Angolifie_Db
IF NOT EXISTS (SELECT * FROM user_table_Angolifie WHERE USER id = 1124)
BEGIN
ALTER TABLE user_table_gomsle
ADD user_img varchar(MAX)
END
GO"

myQueryArray[2] = "ALTER TABLE gotham_Accessories
ALTER COLUMN stationary_count   INT
go"

but i am not getting the result that way due to 'Go' keyword in db name, table name, comment.

You should take a look at *regular expressions*. This is the adult version of *string.split*, in a way. — Rob, Jun 14 '16 at 07:17
Possible duplicate of [Executing SQL batch containing GO statements in C#](http://stackoverflow.com/questions/25563876/executing-sql-batch-containing-go-statements-in-c-sharp) — Adrian Iftode, Jun 14 '16 at 08:10

Alexander Petrov · Accepted Answer · 2016-06-14T07:46:28.470

2

var options = RegexOptions.Multiline | RegexOptions.IgnoreCase;

string[] myQueryArray = Regex.Split(myString, @"^\s*GO\s*$", options);

But even this solution may be incorrect, in that case, if the sql is written as follows:

select
    ID,
    GO
from tableName;

where GO - column name on single line.

Therefore, the only fully working solution would be a sql parser.

edited Jun 14 '16 at 07:46

answered Jun 14 '16 at 07:36

Alexander Petrov

13,457
2
20
49

Your regex working correctly but it case sensitive with respect to "GO" what if scripts ends with "Go" or "go". – Mustafa Gaziani Jun 14 '16 at 08:05
Hi, @Alexander , thank you its work i just update the regex like: Regex.Split(sqlQuery, @"^\s*GO|Go|go|gO\s*$", RegexOptions.Multiline); – Mustafa Gaziani Jun 14 '16 at 08:26
@MustafaGaziani - see update. Use `RegexOptions.IgnoreCase`. – Alexander Petrov Jun 14 '16 at 08:37

score 1 · Answer 2 · answered Jun 14 '16 at 07:25

1

Each of your scripts is followed by a new line, this is represented by \n or \r in a string.

You could try splitting your string using "GO\r", "GO\n" or "GO\r\n"

answered Jun 14 '16 at 07:25

mark_h

5,233
4
36
52

score -1 · Answer 3 · answered Jun 14 '16 at 07:27

-1

You can try this:

string test = "select * from table1 GO select * from table2";
string[] myQueryArray = Regex.Split(test, "GO");

Hope this will help you. Thanks.

answered Jun 14 '16 at 07:27

Sk. Tajbir

290
2
9

2

This will still trigger both if the keyword GO is found, and when GO is randomly found inside other places, like if a table was named TableGOFoo – Stian Skjelstad Jun 14 '16 at 07:36
in the above problem Mustafa tried to split using Go which tells there is no situation like you have mentioned but then again why wasting time on the situation which MIGHT come some day? obviously what you have mentioned is totally understandable. – Sk. Tajbir Jun 14 '16 at 08:05
Its not working in case if there comes GO in comments or db_name or table name – Mustafa Gaziani Jun 14 '16 at 08:06
@Mustafa: then why are you splitting using Go? – Sk. Tajbir Jun 14 '16 at 08:07
Thats the issue I have mentioned its not working, the regex provided by @Alexander Petrov working fine in my case. – Mustafa Gaziani Jun 14 '16 at 08:10

Extract sub Strings From a String

3 Answers3