{"id":343,"date":"2010-07-21T04:47:59","date_gmt":"2010-07-21T09:47:59","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=343"},"modified":"2013-04-17T21:24:21","modified_gmt":"2013-04-18T02:24:21","slug":"replace-constant-in-sql-with-sql-variables","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/","title":{"rendered":"Replace constant in SQL with SQL variables"},"content":{"rendered":"<p>Hard code string constant in SQL query is not a good practice, it should be replaced by SQL variable. General SQL Parser can help you to achieve this quickly.<\/p>\n<p>Here is an insert statement with string constant, those string constants can be replaced by SQL variable in your program automatically.<\/p>\n<pre>\r\nINSERT INTO employee(employeename,lastname) VALUES ('arun','deep')\r\n<\/pre>\n<p>SQL after replace string constant<\/p>\n<pre>\r\nINSERT INTO employee(employeename,lastname) VALUES (@var1,@var2)\r\n<\/pre>\n<p>Code in C# to achieve what we mentioned above.<\/p>\n<pre>\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Text;\r\nusing gudusoft.gsqlparser;\r\nusing gudusoft.gsqlparser.Units;\r\n\r\nnamespace replaceconstwithvar\r\n{\r\n    class replaceconstwithvar\r\n    {\r\n        static void Main(string[] args)\r\n        {\r\n            \/\/ create a sql parser instance supports ms sql server sql dialect\r\n            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);\r\n\r\n            sqlparser.SqlText.Text = @\"INSERT INTO employee(employeename,lastname) VALUES ('arun','deep')\";\r\n            int iRet = sqlparser.Parse();\r\n            int counter = 0;\r\n            if (iRet == 0)\r\n            {\r\n                foreach (TCustomSqlStatement sql in sqlparser.SqlStatements)\r\n                {\r\n                    foreach (TSourceToken st in sql.SourceTokenList)\r\n                    {\r\n                        if \r\n                            (\r\n                            (st.TokenType == TTokenType.ttNumber)\r\n                            || (st.TokenType == TTokenType.ttSQString)\r\n                            )\r\n                        {\r\n                            counter++;\r\n                            st.AsText = \"@var\" + counter.ToString();\r\n\r\n                        }\r\n                    }\r\n\r\n                    Console.WriteLine(\"{0}\", sql.AsText);\r\n                }\r\n            }\r\n            else\r\n            {\r\n                Console.WriteLine(\"Syntax error found in input sql:\");\r\n                Console.WriteLine(sqlparser.ErrorMessages);\r\n            }\r\n\r\n        }\r\n    }\r\n}\r\n<\/pre>\n<p>Download this demo: <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/replaceconstwithvar\/replaceconstwithvar.cs\">C# version<\/a>, <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/modifysql\/replaceConstant.java\">Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hard code string constant in SQL query is not a good practice, it should be replaced by SQL variable. General SQL Parser can help you to achieve this quickly. Here is an insert statement with string constant, those string constants can be replaced by SQL variable in your program automatically. INSERT INTO employee(employeename,lastname) VALUES (&#8216;arun&#8217;,&#8217;deep&#8217;) SQL after replace string constant INSERT INTO employee(employeename,lastname) VALUES (@var1,@var2) Code in C# to achieve what we mentioned above. using System; using System.Collections.Generic; using System.Text; using gudusoft.gsqlparser; using gudusoft.gsqlparser.Units; namespace replaceconstwithvar { class replaceconstwithvar { static void Main(string[] args) { \/\/ create a sql parser\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Replace constant in SQL with SQL variables\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Replace constant in SQL with SQL variables\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-18T02:24:21+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-07-21T09:47:59+00:00\",\"dateModified\":\"2013-04-18T02:24:21+00:00\",\"description\":\"Replace constant in SQL with SQL variables\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Replace constant in SQL with SQL variables\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Replace constant in SQL with SQL variables","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Replace constant in SQL with SQL variables","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/","og_site_name":"SQL and Data Blog","article_modified_time":"2013-04-18T02:24:21+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-07-21T09:47:59+00:00","dateModified":"2013-04-18T02:24:21+00:00","description":"Replace constant in SQL with SQL variables","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/replace-constant-in-sql-with-sql-variables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Replace constant in SQL with SQL variables"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/343"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=343"}],"version-history":[{"count":7,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/343\/revisions"}],"predecessor-version":[{"id":1564,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/343\/revisions\/1564"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}