PATINDEX SQL Server Example

SQL Server > Built-in Functions > PATINDEX

PATINDEX returns the start position of pattern occurrence in an expression.

Warning! If pattern or expression is NULL, PATINDEX returns NULL.

Example

SELECT 'This is PATINDEX example' AS 'Expression'

SELECT PATINDEX('%pat%', 'This is PATINDEX example') as 'Find any position pat';

SELECT PATINDEX('%a_p%', 'This is PATINDEX example') as 'Find any a followed by any caracter and then p';

MAX SQL Server Example

SQL Server > Built-in Functions > MAX

MAX returns the maximum value.

Warning! Null values are ignored!

Example

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p3', null,2) ,   (2,'p4', 50,2)

select * from #LocalTempTable

select MAX(Salary) Max_Salary_In_Company from #LocalTempTable

select MAX(Salary) Max_Salary_By_Dept, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Retrieve List of Tables in MS Access File C# Example

C# > Data > OLE DB > Access - all tables

Use GetSchema to get all tables in MS Access

Example

OleDbConnection con = new OleDbConnection();

con.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=file.mdb;User Id=admin;Password=;";

con.Open();

DataTable userTables = null;
string[] restrictions = new string[4];
restrictions[3] = "Table";

userTables = con.GetSchema("Tables", restrictions);

for (int i = 0; i < userTables.Rows.Count; i++)
   lstTables.Items.Add(userTables.Rows[i][2].ToString());

con.Close();

RTRIM SQL Server Example

SQL Server > Built-in Functions >  RTRIM

RTRIM returns a sting after truncate trailing blanks.

Example:

DECLARE @str varchar(60);

SET @str =  'six spaces after      ';
SELECT @str + '/';
SELECT RTRIM(@str) + '/';

results:

six spaces after      /
six spaces after/

LTRIM SQL Server Example

SQL Server > Built-in Functions >  LTRIM

LTRIM returns a sting after removes leading blanks.






Example:

DECLARE @str varchar(60);

SET @str = '     six spaces before';
SELECT @str;
SELECT LTRIM(@str);

results:

     six spaces before
six spaces before

Average without Zero values SQL Server

SQL Server > Built-in Functions > AVG > Without zerous
 
Example to calculate average without zero

create table #sale(regionid int, amount int)

insert into #sale(regionid , amount ) values (1,100), (2,50) , (3,0)
select
       AVG (CASE WHEN amount <> 0 THEN amount ELSE NULL END)
from
   #sale
drop table #sale

result:
75
  

How to delete rows from DataGridView without delete rows from DataTable

C# > System.Data   > DataGridView > Delete rows

How to delete rows from DataGridView without delete rows from DataTable?

Solution

Copy original DataTable

Example:

  DataTable dt1 = dt.Copy();

  dataGridView1.DataSource = dt1;