萬用字元
SQLServer 的 LIKE 及 PATINDEX 函數字串比對可使用下列萬用字元作為模式。
% 代表任何具有零或多個字元的字串。
WHERE title LIKE '%computer%'
找出書名中含有 'computer' 這個字的所有書名。
_(底線) 任何單一字元。
WHERE au_fname LIKE '_ean'
可找出所有以 ean 結尾的四個字母的名字 (如 Dean、Sean 等)。
[ ]在指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單一字元。
WHERE au_lname LIKE '[C-P]arsen'
可找出結尾是 arsen，開頭是 C 和 P 之間的任何單一字元的作者姓氏，例如，Carsen、Larsen、Karsen 等等。
[^] 不在指定範圍 ([^a-f]) 或集合 ([^abcdef]) 中的任何單一字元。
WHERE au_lname LIKE 'de[^l]%'
所有開頭是 de 且下一個字元不是 l 的作者姓氏。
LIKE
判斷特定字元字串是否符合指定的模式。
範例：
SQLServer 的 LIKE 及 PATINDEX 函數字串比對可使用下列萬用字元作為模式。
% 代表任何具有零或多個字元的字串。
WHERE title LIKE '%computer%'
找出書名中含有 'computer' 這個字的所有書名。
_(底線) 任何單一字元。
WHERE au_fname LIKE '_ean'
可找出所有以 ean 結尾的四個字母的名字 (如 Dean、Sean 等)。
[ ]在指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單一字元。
WHERE au_lname LIKE '[C-P]arsen'
可找出結尾是 arsen，開頭是 C 和 P 之間的任何單一字元的作者姓氏，例如，Carsen、Larsen、Karsen 等等。
[^] 不在指定範圍 ([^a-f]) 或集合 ([^abcdef]) 中的任何單一字元。
WHERE au_lname LIKE 'de[^l]%'
所有開頭是 de 且下一個字元不是 l 的作者姓氏。
判斷特定字元字串是否符合指定的模式。
範例：
A.找出區碼是 07 的所有電話號碼。
WHERE PhoneNumber LIKE '(07)%'
B.使用 NOT LIKE 搭配 % 萬用字元
找出區碼非 07 的所有電話號碼。
WHERE ph.PhoneNumber NOT LIKE '(07)%'
C.使用 ESCAPE 子句
下列範例會利用 ESCAPE 子句和逸出字元來尋找資料行中完全相符的 10-15% 字元字串。
WHERE discount LIKE '%10-15!% off%' ESCAPE '!'
D.使用 [ ] 萬用字元
下列範例會在 Person 資料表上尋找名字為 Cheryl 或 Sheryl 的員工。
WHERE FirstName LIKE '[CS]heryl';
下列範例會在 Person 資料表上尋找姓氏為 Zheng 或 Zhang 的員工。
SELECT LastName, FirstName
FROM Person.Person
WHERE LastName LIKE 'Zh[ae]ng'
ORDER BY LastName ASC, FirstName ASC;
字串比對函數
PATINDEX
PATINDEX ( '%pattern%' , expression )
傳回指定模式，在文字中第一次出現的起始位置，如果找不到便傳回零。
A.搭配 PATINDEX 使用模式
下列範例會尋找 ensure 模式在 Document 資料表中 DocumentSummary 資料行之特定資料列中的起始位置。
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document WHERE DocumentNode = 0x7B40;
如果您沒有利用 WHERE 子句來限制要搜尋的資料列，查詢會傳回資料表的所有資料列，且針對找到模式的所有資料列報告非零值，找不到模式的所有資料列則會報告零。
B.搭配 PATINDEX 使用萬用字元
指定的字串中 (索引從 1 開始)，使用 % 和 _ 萬用字元來尋找模式 'en' 後面接著任何一個字元和 'ure' 的開始位置：
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');
PATINDEX 的運作方式就像 LIKE，所以可用萬用字元。
不必用百分比括住模式。
PATINDEX('a%', 'abc') 會傳回 1 且 PATINDEX('%a', 'cba') 會傳回 3。
與 LIKE 不同的是，PATINDEX 會傳回位置，類似 CHARINDEX。
C.搭配 PATINDEX 使用 COLLATE
下列範例利用 COLLATE 函數來明確指定所搜尋之運算式的定序。
SELECT PATINDEX ( '%ein%', 'Das ist ein Test' COLLATE Latin1_General_BIN) ;
D.使用變數來指定模式
下列範例會使用變數，將值傳遞給 pattern 參數。
USE AdventureWorks2012; GO DECLARE @MyValue varchar(10) = 'safety';
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
下列範例會尋找 ensure 模式在 Document 資料表中 DocumentSummary 資料行之特定資料列中的起始位置。
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document WHERE DocumentNode = 0x7B40;
如果您沒有利用 WHERE 子句來限制要搜尋的資料列，查詢會傳回資料表的所有資料列，且針對找到模式的所有資料列報告非零值，找不到模式的所有資料列則會報告零。
B.搭配 PATINDEX 使用萬用字元
指定的字串中 (索引從 1 開始)，使用 % 和 _ 萬用字元來尋找模式 'en' 後面接著任何一個字元和 'ure' 的開始位置：
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');
PATINDEX 的運作方式就像 LIKE，所以可用萬用字元。
不必用百分比括住模式。
PATINDEX('a%', 'abc') 會傳回 1 且 PATINDEX('%a', 'cba') 會傳回 3。
與 LIKE 不同的是，PATINDEX 會傳回位置，類似 CHARINDEX。
C.搭配 PATINDEX 使用 COLLATE
下列範例利用 COLLATE 函數來明確指定所搜尋之運算式的定序。
SELECT PATINDEX ( '%ein%', 'Das ist ein Test' COLLATE Latin1_General_BIN) ;
D.使用變數來指定模式
下列範例會使用變數，將值傳遞給 pattern 參數。
USE AdventureWorks2012; GO DECLARE @MyValue varchar(10) = 'safety';
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
CHARINDEX (Transact-SQL)
SQL Server 2012在運算式中搜尋另一個運算式，並在找到時傳回它的開始位置。
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
引數
expressionToFind
這是字元運算式，其中包含要尋找的順序。 expressionToFind 的限制為 8000 個字元。expressionToSearch
這是要搜尋的字元運算式。start_location
這是搜尋開始的 integer 或 bigint 運算式。 如果未指定 start_location，或者它是負數或 0，搜尋就會從 expressionToSearch 開頭開始。
傳回類型
如果 expressionToSearch 的資料類型是 varchar(max)、nvarchar(max) 或 varbinary(max)，則傳回 bigint，否則傳回 int。
備註
如果 expressionToFind 或 expressionToSearch 是 Unicode 資料類型 (nvarchar 或 nchar)，但另一項不是，則另一項也會轉換成 Unicode 資料類型。CHARINDEX 不能用於 text、ntext 和 image 資料類型。
如果 expressionToFind 或 expressionToSearch 是 NULL，CHARINDEX 會傳回 NULL。
如果在 expressionToSearch 內找不到 expressionToFind，CHARINDEX 會傳回 0。
CHARINDEX 會以輸入的定序為基礎來執行比較。 若要執行指定定序的比較，您可以利用 COLLATE，將明確的定序套用至輸入上。
傳回的開始位置是以 1 為基準，而不是以 0 為基準。
0x0000 (char(0)) 是 Windows 定序中未定義的字元，而且不得包含在 CHARINDEX 中。
增補字元 (Surrogate 字組)
使用 SC 定序時，start_location 和傳回值會將 Surrogate 字組計算成一個字元，而不是兩個字元。 如需詳細資訊，請參閱＜定序與 Unicode 支援＞。
範例
A.傳回運算式的開始位置
下列範例會傳回 bicycle 字元序列在 Document 資料表 DocumentSummary 資料行中的起始位置。
USE AdventureWorks2012; GO DECLARE @document varchar(64); SELECT @document = 'Reflectors are vital safety' + ' components of your bicycle.'; SELECT CHARINDEX('bicycle', @document); GO
以下為結果集：
----------- 48
B.從特定位置執行搜尋
下列範例會利用選擇性的 start_location 參數，在 DocumentSummary 資料行的第五個字元開始搜尋 vital。
USE AdventureWorks2012; GO DECLARE @document varchar(64); SELECT @document = 'Reflectors are vital safety' + ' components of your bicycle.'; SELECT CHARINDEX('vital', @document, 5); GO
以下為結果集：
----------- 16 (1 row(s) affected)
C.搜尋不存在的運算式
下列範例會顯示在 expressionToSearch 內找不到 expressionToFind 時的結果集。
USE AdventureWorks2012; GO DECLARE @document varchar(64); SELECT @document = 'Reflectors are vital safety' + ' components of your bicycle.'; SELECT CHARINDEX('bike', @document); GO
以下為結果集：
-----------
0
(1 row(s) affected)
D.執行區分大小寫的搜尋
下列範例會在 'Das ist ein Test' 中，以區分大小寫的方式搜尋 'TEST' 字串。
USE tempdb; GO --perform a case sensitive search SELECT CHARINDEX ( 'TEST', 'Das ist ein Test' COLLATE Latin1_General_CS_AS);
以下為結果集：
-----------
0
下列範例會在 'Das ist ein Test' 中，以區分大小寫的方式搜尋 'Test' 字串。
USE tempdb; GO SELECT CHARINDEX ( 'Test', 'Das ist ein Test' COLLATE Latin1_General_CS_AS);
以下為結果集：
-----------
13
E.執行不區分大小寫的搜尋
下列範例會在 'Das ist ein Test' 中，以不區分大小寫的方式搜尋 'TEST' 字串。
USE tempdb; GO SELECT CHARINDEX ( 'TEST', 'Das ist ein Test' COLLATE Latin1_General_CI_AS) GO
以下為結果集：
-----------
13
沒有留言:
張貼留言