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
– Rob, Commented Jun 14, 2016 at 7:17
Possible duplicate of Executing SQL batch containing GO statements in C# — Adrian Iftode
– Adrian Iftode, Commented Jun 14, 2016 at 8:10

Alexander Petrov · Accepted Answer · 2016-06-14 07:46:28Z

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, 2016 at 7:46

answered Jun 14, 2016 at 7:36

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mustafa Gaziani Over a year ago

Your regex working correctly but it case sensitive with respect to "GO" what if scripts ends with "Go" or "go".

Mustafa Gaziani Over a year ago

Hi, @Alexander , thank you its work i just update the regex like: Regex.Split(sqlQuery, @"^\s*GO|Go|go|gO\s*$", RegexOptions.Multiline);

Alexander Petrov Over a year ago

@MustafaGaziani - see update. Use RegexOptions.IgnoreCase.

mark_h · Accepted Answer · 2016-06-14 07:25:18Z

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, 2016 at 7:25

mark_h

5,5494 gold badges44 silver badges61 bronze badges

Comments

Sk. Tajbir · Accepted Answer · 2016-06-14 07:27:59Z

-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, 2016 at 7:27

Sk. Tajbir

2902 silver badges9 bronze badges

5 Comments

Stian Skjelstad Over a year ago

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

Sk. Tajbir Over a year ago

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.

Mustafa Gaziani Over a year ago

Its not working in case if there comes GO in comments or db_name or table name

Sk. Tajbir Over a year ago

@Mustafa: then why are you splitting using Go?

Mustafa Gaziani Over a year ago

Thats the issue I have mentioned its not working, the regex provided by @Alexander Petrov working fine in my case.

Collectives™ on Stack Overflow

Extract sub Strings From a String

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related