Like

---使用北風資料庫
Use Northwind

/*
Like '%_[][^]'
*/

--列出'A'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%'

--列出'S'結尾客戶
select *
from dbo.Customers
where CompanyName like '%s'

--列出客戶名稱有含'B'的客戶
select *
from dbo.Customers
where CompanyName like '%B%'

--列出電話倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '%2_'

--列出電話第二個字是'2',倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '_2%2_'

--列出'A' 或'B'或'C'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%' or CompanyName like 'B%' or CompanyName like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[A-C]%'

--列出Not 'A' 且Not 'B'且Not 'C'開頭客戶
select *
from dbo.Customers
where CompanyName not like 'A%' and CompanyName not like 'B%' and CompanyName not like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[^ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[^A-C]%'

-------------------------test
update dbo.Products
set QuantityPerUnit='70%'
where ProductID=1

--Select *
from dbo.Products
where ProductID=1

--列出有%產品
Select *
from dbo.Products
where QuantityPerUnit like '%#%%' escape '#'

比較運算子

---使用北風資料庫
Use Northwind

/*
>, < ,= ,>= ,<= ,<>
*/

--列出延遲出貨訂單
SELECT OrderID,OrderDate,ShippedDate,RequiredDate
From dbo.Orders
where ShippedDate>RequiredDate

--列出非美國地區客戶
Select CustomerID,CompanyName,City,Country
From dbo.Customers
where Country<>'USA'

--列出庫存量不足的產品
Select ProductID,ProductName,UnitPrice,UnitsOnOrder,UnitsInStock
from dbo.Products
where UnitsOnOrder>UnitsInStock

--列出安全存量不足的產品Select ProductID,ProductName,UnitPrice,UnitsOnOrder,ReorderLevel
from dbo.Products
where UnitsOnOrder>ReorderLevel

Select

---使用北風資料庫
use Northwind


/*
Select Column1,Column2,... (垂直篩選)
From Table
Where Row條件式 (水平篩選)
*/


--------All
select *
from dbo.Customers


--列出美國客戶
select CustomerID,CompanyName,City,Country --垂直篩選
from dbo.Customers
where Country='USA' --水平篩選


---------All
Select *
from dbo.Employees


--列出1960年後出生的員工
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where BirthDate>='1960/1/1'


--------使用YEAR(日期格式)
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where Year(BirthDate)>=1960

------All
Select *
from dbo.Products

--列出單位售價>=100元以上的產品
Select ProductID,ProductName,UnitPrice
from dbo.Products
where UnitPrice>=100

reason 3028

FTP時發生錯誤訊息，並只傳部分資料：
Unable to convert data from CCSID 937 to CCSID 950: reason 3028.

有DBCS(雙位元組字元集)字元時因0E/0F不成對，傳到該筆資料就會因無法轉換而終止。

字串連接

MySQL/Oracle例:
SELECT CONCAT(last_name,first_name) FROM person

Oracle/DB2例:
SELECT last_name ││first_name FROM person

SQL Server例:
SELECT last_name + first_name FROM person

字串替換

語法：
Replace([欄位] , '替換前字串' , '替換後字串')
Update [資料表] Set [欄位] = Replace([欄位] , '替換前字串' , '替換後字串') Where [條件式]

例：
將字串欄位中含有'abcd'替換成'1234'
UPDATE testtable SET field1 = REPLACE(field1,'abcd','1234')

replace() 遇到NULL時發生錯誤

replace 遇到 Null 值時會發生錯誤，錯誤訊息：Null 的使用不正確: 'replace'


原程式碼:
out = out & replace(asrs.Fields(i).Value,","," ") & ","


改為:
If Not IsNull(as400rs.Fields(i).Value) Then
out = out & replace(asrs.Fields(i).Value,","," ") & ","
Else
out = out & " " & ","
End If