RegEx for SQL Server string to replace with unicode prefix

Question

i have a similar problem like this: .NET Regex for SQL Server string... but not Unicode string?

The RegEx (?:N'(?:''|[^'])*'[^']*)*(?<!N)'(?<value>(?:''|[^'])*)' doesn't match this string correctly:

Insert into SomeTable (someColumns) values ('someValue', N'someValue', 'someValue')

it recognizes "N'someValue', 'someValue'" as a Match

I cant figure out to correct the RegEx to match all string literals but not the literals with the N-Prefix.

Like mentioned in the Link above the RegEx have to ignore escaped quotes in the space of the string like 'some '' escaped'

Something tells me it's better to solve this without REGEX. – Cristian Lupascu Jul 26 '13 at 07:37 — Cristian Lupascu, Jul 26 '13 at 07:37

score 1 · Accepted Answer · edited May 23 '17 at 11:57

In my opinion, there is a better tool for your job - the TSql100Parser class:

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.Collections.Generic;
using System.IO;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        IList<ParseError> errors = new List<ParseError>();
        var tsql = @"
                Insert into SomeTable (someColumns) 
                values ('someValue1', 
                        N'someValue2', 
                        'someValue3',
                        'some '' escaped')";
        var result = GetLiterals(tsql);
    }

    private static List<string> 
        GetLiterals(string strQuery)
    {
        var parser = new TSql100Parser(false);
        IList<ParseError> errors = new List<ParseError>();
        var result = 
            parser.GetTokenStream(new StringReader(strQuery), errors);
        return result
            .Where(t =>
                t.TokenType == TSqlTokenType.AsciiStringLiteral ||
                t.TokenType == TSqlTokenType.UnicodeStringLiteral)
            .Select(t => t.Text)
            .ToList();
    }
}

You can't use Type-3 grammars (regular expression) to parse Type-0 grammars (T-SQL). The same stands when you try to parse HTML. It will not be 100% fail proof in real life.

thank you for your answer, unfortunately i have to implement it in delphi :( I managed to implement it with a less complex RegEx to find the String literals and some manual parsing — deterministicFail, Jul 26 '13 at 09:15
Maybe you've already read it, but if not, this question's answers could be helpful: [Need in SQL parser on Delphi](http://stackoverflow.com/questions/615608/need-in-sql-parser-on-delphi). — Alex Filipovici, Jul 26 '13 at 09:25

RegEx for SQL Server string to replace with unicode prefix

1 Answers1