SQL 字串index

IF PATINDEX('%' + @pattern + '%', @Expression) = 0 找不到
IF PATINDEX('%' + @pattern + '%', @Expression) = 1 字串位於第一個位置

SQL 字串相加

StrA + StrB

SQL Table種類

Local Temporary Tables #TempTable
Global Temporary Tables ##TempTable
Permanent Tables TableName
Table Variables @TempTable

CREATE TABLE #TempTable
(
id INT,
name VARCHAR(32)
)

CREATE TABLE ##TempTable
(
id INT,
name VARCHAR(32)
)

CREATE TABLE TableName
(
id INT,
name VARCHAR(32)
)

DECLARE @TempTableTABLE
(
id INT,
name VARCHAR(32)
)

註冊Windows服務

註冊
C:\Windows\Microsoft.NET\Framework\v2.0.50727\
C:\Windows\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe

或是
path="C:\Windows\Microsoft.NET\Framework\v2.0.50727\"
InstallUtil.exe ServiceName.exe

移除
InstallUtil.exe /u ServiceName.exe

IIS Recycle AppPool List AppPool

IIS 6
iisapp /a DefaultAppPool /r
c:\windows\system32\iisapp.vbs /a YourPoolName /r
cscript.exe c:\windows\system32\iisapp.vbs /a YourPoolName /r

IIS7
appcmd recycle apppool "MyAppPool"
%windir%\system32\inetsrv\appcmd.exe recycle apppool "MyAppPool"

列出
IIS6
iisapp

IIS7
appcmd.exe list wp

Regex Pattern Name

(subexpression) - (Matched Subexpressions, 相符子樣式)
(?subexpression) - (Named Matched Subexpressions, 命名之相符子樣式)

Dim reg_exp As New Regex("recycleapppool\s(?<AppPoolName>[\w]+)")
m.Groups("AppPoolName").Value

感謝

[入門][Regex] Regular Expression 詳論
[Regex] 進階群組建構

SQL 分組 分類 計算個數

SELECT DISTINCT GroupField1, COUNT(*) AS CountField
FROM SourceTableName
GROUP BY GroupField1
HAVING COUNT(*) > 1
ORDER BY CountField

SQL 分組排名

----以相同GroupField為組，依據SortField排序，並給予排名RANK
SELECT RANK() OVER (PARTITION BY SourceTableName.GroupField ORDER BY SourceTableName1.SortField1, SourceTableName1.SortField2 ) AS 'RANK', *
FROM SourceTableName1
WHERE ConditionField1 IN
(

SELECT DISTINCT ConditionField1
FROM SourceTableName2
GROUP BY ConditionField1
HAVING COUNT(*) > 1
)
ORDER BY SourceTableName1.SortField1 ASC

SQL 迴圈

--宣告Cursor的資料來源Table
DECLARE vendor_cursor CURSOR FOR
SELECT Field1, Field2, Field3
FROM SourceTableName
WHERE Field4 IN
(
SELECT DISTINCT Field4
FROM ConditionTableName
GROUP BY Field4
HAVING COUNT(*) > 1
)
ORDER BY Field1 , Field2 DESC

--開啟Cursor
OPEN vendor_cursor

--Cursor第一次讀取資料
--資料INTO要與來源Table(SourceTableName)的SELECT欄位的順序，格數，數量相同
FETCH NEXT FROM vendor_cursor
INTO @Field1, @Field2, @Field3

--迴圈開始
--判斷迴圈狀態
WHILE @@FETCH_STATUS = 0
BEGIN
--迴圈內想要做的邏輯
--!@#$%^&*
--!@#$%^&*
--!@#$%^&*


--回圈內重複抓取資料
FETCH NEXT FROM vendor_cursor
INTO @Field1, @Field2, @Field3
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor

SQL 宣告變數 設定變數

DECLARE @ID AS INT
DECLARE @LastCust AS VARCHAR(50)
DECLARE @LastReceiveTime AS DATETIME

SET @ThisCust = ''

SQL SELECT成一個新表格

SELECT * INTO TempTable
FROM [SourceTableName]
WHERE SourceTableFile = 'Condition'

SQL 取得特定時間格式

DECLARE @Today DATETIME
SET @Today = GETDATE()

DECLARE @EndDate DATETIME
DECLARE @StrEndDate VARCHAR(8)
SET @EndDate = DATEADD(day, -1, @Today)
SELECT @EndDate
SET @StrEndDate = replace(str(year(@EndDate), 4), ' ', '0') +
replace(str(month(@EndDate),2), ' ', '0') +
replace(str(day(@EndDate),2), ' ', '0')
SELECT @StrEndDate

SQL DROP Table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TableName1]') AND type in (N'U'))

DROP TABLE [TableName1]