Parsing insert queries from SQL Server

I am exporting data from SQL server to an SQL file. The problem is some of the column entries that are strings contain commas so when I try to parse the SQL file (basically using string split with comma delim) I am not getting what I want. Anyone have a solution to this problem or a better idea?

Basically I need to separate the values in the query:

  • I am not able to read and send data into SQL Server through console application
  • Execute multiple Stored Procedures with Quartz.NET
  • How can I setup my SQL Server LocalDB to be as close to a normal SQL Server?
  • SQL Server 2005: T-SQL to temporarily disable a trigger
  • Common denominator (column) in SQL query
  • How to avoid NULL when using Value-Name Mapping in SQL
  • Example:

    … VALUES (123, ‘bob likes to eat berries, and beans’)

    If I split that using a comma as a delimiter I get:

    123
    'bob likes to eat berries
    and beans'
    

  • Error 0x8007F303 occurs during printing of reports from MOSS using SRS viewer web part
  • how to implement undo operation in datagridview
  • Inheritance and association in SQL
  • Disconnected or Connected Architecture
  • Dapper returns different column name
  • How can I programmatically check (parse) the validity of a TSQL statement?
  • 5 Solutions collect form web for “Parsing insert queries from SQL Server”

    Sounds like you need to export using a different delimiter. What are you using to export? If it’s the BCP command line utility, you could switch delimiters using the -t switch. For example, -t | for pipe-delimited.

    Have you tried exporting as Tab seperated variables?

    Are you constrained to using a comma as a delimiter? You could use a different character or series of characters you do not expect to show up in the strings.

    Alternatively, depending on the data set, you could “encode” the strings in a temporary table and swap commas for a special character that you can identify later.

    You can use a regular expression.

    var myMatches = Regex.Matches(lineInFile, "(?:[^',]+)|(?:'[^']+')");
    foreach(var m in myMatches)
    {
       /* write m.ToString() to StringBuilder or file */
    }
    

    Hope it helps.

    If you have to use the current format, you could you a regex like

    ((\d+), ('.*'))
    

    to split it. The regex is not the best, but works with you exampe.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.