While (c#)

C# > Statements > while

While statement executes a statement or a block of statements until a specified expression evaluates to false.
A while loop can be terminated with

• break
• goto
• return
• throw

Pass control to the next iteration with the continue statement.
   
Example:

int i = 0;

while (i < 10)
{
     if (i == 1)
   {
         i = 2;
     continue; // goto to next loop
   }
   if (i == 2)
     return; // exit while
   if (i==5)
     break; // exit while
  
   i= i + 1;
}

 

MS Excel: Concatenate strings

Excel > Operators> Concatenate

To concatenate multiple strings into a single string in Excel, you can use the "&" operator to separate the string values.

string_1 & string_2 & string_n

SQL SELECT multi-columns INTO multi-variable

SQL Server > Scripts >  SELECT multi columns INTO multi variables

Example:

CREATE TABLE #tmp

(

    id       int PRIMARY KEY,

    name     varchar(20),
    age             int
)
GO

INSERT #tmp(id, name, age) VALUES (1, 'John',20)

INSERT #tmp(id, name, age) VALUES (2, 'Dan',40)
GO

declare @minage int,

        @maxage int
SELECT
   @minage = min(age),
   @maxage = max(age)
FROM
   #tmp
select
   @minage as minage, @maxage as maxage

drop table #tmp

While SQL Server

SQL Server >  Control of Flow > While

While represents repeated execution of an SQL statement or statement block.

• as long as the specified condition is true the statements are executed repeatedly 
• the execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords

Syntax:

WHILE Boolean_expression { statement_block | BREAK | CONTINUE }


Example

declare @t nvarchar(max)

declare @t1 nvarchar(max)
declare @p int

set @t = 'fld1:"val1";fld2:"val2","val3";fld3:"val4";'

set @p = CHARINDEX(';', @t);

while @p > 0
begin
     set @t1 = SUBSTRING (@t, 0, @p)
     set @t = SUBSTRING (@t, @p+1, len(@t) - @p)
     set @p = CHARINDEX(';', @t);
        select @t1
     if @p = 0
      break
    else
      continue
end

Result:

CHARINDEX (SQL Server)

SQL Server > Built-in Functions > CHARINDEX

CHARINDEX SQL Server function searches an expression for another expression and returns its starting position if found.

Example

declare @t nvarchar(max)

set @t = 'blue; red; green'

SELECT CHARINDEX(';', @t);

Result
(No column name)

5

SELECT CHARINDEX(':', @t);

Result
(No column name)

0

timestamp (Transact-SQL)

SQL Server > Data types > TimeStamp

Timestamp in SQL Server is a data type that exposes automatically generated, unique binary numbers within a database.

• is generally used as a mechanism for version-stamping table rows. 
• the storage size is 8 bytes. 
• the timestamp data type is just an increment number and does not preserve a date or a time. 
• to record a date or time, use a datetime data type.

Example

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL

       DROP TABLE tbl1;
GO

CREATE TABLE tbl1

(
       id     int,
       code   timestamp
);
GO

INSERT INTO tbl1(id) values  (1)

SELECT
       *
FROM
       tbl1

Result:

id code
1  0x00000000005C0E69

Update table join SQL Server

SQL Server > Update > Using Join


UPDATE
  tbl1 AS A
  INNER JOIN tbl2 AS B ON A.F1=B.F1
SET
  A.X = 1,
  A.Y = 2
WHERE
  A.id = 100