Parse SQL Insert statement and get all string values between N'VALUE'

Question

I have a large SQL Server insert script

INSERT [dbo].[Table] ([Key], [Name], [Value], [Module]) 
VALUES (1, N'Product', N'Value 1', N'Value 2')

Now I need to parse this large file, line by line and for each value that is NVARCHAR N'VALUEHERE' I want to replace it with some custom info I fetch from a database.

If this is my script:

INSERT [dbo].[Table] ([Key], [Name], [Value], [Module]) 
VALUES (1, N'Product', N'Value 1', N'Value 2')

After I parse it I want to update it to:

INSERT [dbo].[Table] ([Key], [Name], [Value], [Module]) 
VALUES (1, N'Product Changed', N'Value 1 with some custom info', N'Value 2 with different info')

Of course the real scenario is a bit different.

In big, I want to fetch all nvarchar values and for each value that starts with N' get that value from single quotes, do some operation on it and update it with the new value, then move to next N' and so on.

My question:

How can I fetch every single value between quotes N'VALUE' change that value and replace with the new value N'CHANGED'?

string line = null;


StreamReader file = new System.IO.StreamReader(@"c:\script.sql");  
while((line = file.ReadLine()) != null)  
{  
       // work with `line` here to get each value 
}

One more issue, how do I fetch the value if it's something like:

 `N'Jimm''s device is broken'`

As I mentioned the scenario is a bit different, The info coming from a rest api therefore I need to do it with C#. Sure this will have been great but I don't have access to db directly where I will fetch the info from — user2818430, Dec 31 '19 at 17:18
Is the question just how to update a text file and replace the values, or does it involve getting the data back into the database? — Noel, Dec 31 '19 at 17:29
Are you just appending some value to every value that's in quotes? Or is the replacement supposed to be different for every value? — devNull, Dec 31 '19 at 17:38
I removed the sql tag because this has nothing to do with SQL. It is about transforming a text file. — Gordon Linoff, Dec 31 '19 at 17:38
Use the regex replace overload that takes a delegate then have your conditional logic inside the delegate to get whatever the appropriate replacement text is — Martin Smith, Dec 31 '19 at 17:39
Does this answer your question? [How can I read and replace Strings line by line in a Text File?](https://stackoverflow.com/questions/32274427/how-can-i-read-and-replace-strings-line-by-line-in-a-text-file) — devNull, Dec 31 '19 at 17:43
Have you looked at [`TSqlParser`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser?view=sql-dacfx-140.3881.1)? There are several other results if you use to search for `tsql parser`. That would be the right way to avoid getting lost in comments, quoted strings, ... in the DML. — HABO, Dec 31 '19 at 18:42
One way is to create a string array : string[] valuesArray : {"1", "N'Product'", "N'Value 1'", "N'Value 2'"}; Then create a string values = string.Join(",", valuesArray); Finally add the string to the query : string query = string.Format("INSERT [dbo].[Table] ([Key], [Name], [Value], [Module]) VALUES ({0})", values); — jdweng, Dec 31 '19 at 20:45

Xeno · Answer 1 · 2019-12-31T22:41:43.873

I'm assuming you want to do some logic on the values between the single quotes. If you just want a simple string replacement then regex replace would be better.

You can use regex matches and their capture groups to select a pieces of the regex match. Then you can use the capture groups string index values to manipulate your original string.

string input = @"INSERT [dbo].[Table] ([Key], [Name], [Value], [Module]) VALUES (1, N'Product', N'Value 1', N'Value 2')";

Regex regex = new Regex("N'([^']+)'");
MatchCollection matches = regex.Matches(input);

//Loop though matches backwards so the index values don't get misaligned
for (int i = matches.Count - 1; i >= 0; i--)
{
    //Get the capture group info for the content between the single quotes
    Group captureGroup = matches[i].Groups[1];

    //Replace the contents of the input string with some updated value
    input = input.Substring(0, captureGroup.Index) + SomeStringMethod(captureGroup.Value) + input.Substring(captureGroup.Index + captureGroup.Length);
}

Console.WriteLine(input);

Parse SQL Insert statement and get all string values between N'VALUE'

1 Answers1