SQL Wave Char

Today, My colleague asked me a question that Entity Framework will generate SQL and has ESCAPE '~' at the end of the SQL.

SELECT * FROM T WHERE Title LIKE 'foo%' ESCAPE '~'

After the survey, some of the article that the only things we knew it is ESCAPE are used to query the data has  Universal characters.There are no articles about "~".Through some of the testing, we found it represented all of the Universal characters.Which means you may be used ESCAPE '%_[^' to query has the data include it then you can just use ESCAPE '~' to replace it.
SELECT * FROM T WHERE Title LIKE 'foo%' ESCAPE '%_[]^'
equal
SELECT * FROM T WHERE Title LIKE 'foo%' ESCAPE '~'


The char "~"  represented all of the Universal characters.If we want to query the data has "~" that could be work?
SELECT * FROM T WHERE (Title LIKE '~%' ESCAPE '%_[]^')
result:Cannot be query

Modify SQL，Replace"~" to "~~"
SELECT * FROM T WHERE (Title LIKE '~~%' ESCAPE '%_[]^')
result:OK

Ps: When Entity Framework is querying that will auto detecting if it has "~" or not, it will be replaced to two "~" while finding it.
This function is so convenient, isn't it?

Reference

Popular posts from this blog

VS2017-Build fail-FindDependenciesOfExternallyResolvedReferences

Image
Recently, After installed the newest version of Visual Studio 2017 that occurred some of the following error messages: Software Infomation:Visual Studio 2017 Enterprise 15.7.4  (The problem since 15.6.x) Workaround I asked the help from forums the got temporary workaround as below: Opening file: C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets Finding the red block highlight at below photo then remove it from the file. Reference https://developercommunity.visualstudio.com/content/problem/208668/the-finddependenciesofexternallyresolvedreferences.html
Keep reading

SQL Server pass parameter to Procedure

Today, My colleague mention his work which how to pass the parameter to the procedure. The workaround as below: 1.Declare a custom table type CREATE   TYPE   dbo . USER_TABLE_TYPE  AS   TABLE   (        USER_NO   VARCHAR ( 8 ) ) 2.C# Declare DataTable and SqlParameter      var   table =   new   DataTable();             table.Columns.Add( new   DataColumn( "USER_NO" , System.Type.GetType( "System.String" )));             table.Rows.Add(table.NewRow());             table.Rows[0][ "USER_NO" ] =   "Abc" ;               var   par =   new   SqlParameter             {                 ParameterName =   "userTable" ,                 Value = table,                 SqlDbType = SqlDbType.Structured,                 TypeName =   "dbo.USER_TABLE_TYPE"             }; 3.Declare parameter which type is just created CREATE   PROCEDURE   dbo . GetUsers        @userTable USER_TABLE_TYPE  READONLY
Keep reading

Jenkins- PowerShell include global config

Image
We have a PowerShell-script that will be referenced in many jobs at Jenkins.e.g. Retrieving a DataTable from the database. It will hard to maintain if this script in individual jobs. For this issue, I found a plugin can solve it How to setup 1.Install plugin-Managed Scripts 2.Jenkins Management,Enter the link of Managed files. 3.Add a new Config Name:For identification。 Content:Execute Script Way to use 1.Including the config which we just add it before.，Target:Output folder 2.Executing Power Shell Import-Module the config file in the output folder and call its function to get the count of records by SQL Reference Managed Script Plugin https://wiki.jenkins.io/display/JENKINS/Managed+Script+Plugin Power Shell Sql Query http://mattslay.com/sql-server-queries-using-powershell-lesson-1/
Keep reading