SQL Server String Manipulations

SQL Server String Manipulations:

We can use simple regular expressions for "LIKE" to filter as wild card characters.We can use [0-9] for digits and [a-z] for characters.
Eg: to filter a 4 digit ZIP code we can use " where ZIP like '[0-9][0-9][0-9][0-9]'"
      to filter the string that starts with digit we can use " where name like '[0-9]%'"

Like same, we can use ^ for the not match condition and _ for matching one character condition.
Eg: to filter names that don't start with p then we can use " where name like '[^p]%'"
      to filter names that end with ir then we can use " where name like '_ir'"

We can find the index of any character in a string expression with CHARINDEX function. To find the last index of any character, we can use the below...

datalength(filename)-charindex('.',reverse(filename),1)

Comments

Post a Comment

Popular posts from this blog

Base 64 encoding and decoding

Encoding and decoding of BASE64 format strings for Apache Tomcat web server.  java -cp /usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/lib/tcServer.jar:/usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/bin/tomcat-juli.jar:/usr/tcServer-6.0/tomcat-6.0.33.B.RELEASE/lib/tomcat-coyote.jar com.springsource.tcserver.security.PropertyDecoder -encode / -decode base64 <password>
Read more

LINQ Queries with GROUP BY, INNER JOIN, COUNT and SUM: Examples

Examples of different LINQ queries in C#: GROUP BY, INNER JOIN, COUNT and SUM Setup the LINQPad against the Northwind database. 1. LINQ query with GROUP BY, JOIN and SUM.     Find the quantities required for each product to fulfill all the orders.     Here the tables we have to use are Products and Order Details. Both of these tables can be joined on    ProductID key and grouped by Product Name to make sure that the Quantity is calculated against a named product instead of ID.     The SQL query for the above requirement is :        s elect A.ProductName,sum(B.Quantity)         from Products P join "Order Details" OD         on A.ProductID=B.ProductID         group by A.ProductName     The equivalent LINQ query for the above is:                 from p in Products         join od in OrderDetails         on p.ProductID equals od.ProductID         group od by p.ProductName into g         select new { ProductName=g.Key, Quanti=g.Sum(a => a.Quantity)}      2. LIN
Read more

How to write Custom delete Confirmation Modal for Kendo Grid in MVC:

How to write Custom delete Confirmation Modal for Kendo Grid in MVC: For the above task, we need to add custom command for delete operation.For the Click, the argument is the javascript function name. .Columns(columns =>columns.Command(command => { command.Custom("Delete").Click("deleteRow"));  Later, you need to tell the kendo Grid that you want to suppress the default behavior for the Delete Action. .Editable(editable => editable.DisplayDeleteConfirmation(false)) Need to remove the destroy property for the datasource mapping to grid. Like .Destroy for the DataSource has to be removed. We are handling this server side work through script logic. Now, write the modal window markup for the Delete pop-up. <div id="modalWindow">     <h2>Are you sure to Delete?</h2>     <span class="k-label">Reason For Deletion</span> <input id="txtReasonForChange" class="k-textbox" />    
Read more