[MS-SQL] 字串處理-取字元 CHARINDEX (Transact-SQL) 查找字元在字串中的位置

語法

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) 

範例

A. 傳回運算式的開始位置

DECLARE @document VARCHAR(64);  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bicycle', @document);  
GO  

-----------   
48

select CHARINDEX(',', '123,56')

-----------
4

B. 從特定位置執行搜尋

DECLARE @document VARCHAR(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('vital', @document, 5);  
GO

-----------   
16

C. 搜尋不存在的運算式

DECLARE @document VARCHAR(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bike', @document);  
GO

-----------
0

D. 執行區分大小寫的搜尋

此範例會顯示已搜尋字串 'This is a Test``' 中字串 'TEST' 的區分大小寫搜尋。

USE tempdb;  
GO  
--perform a case sensitive search  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);

-----------
0

此範例會顯示 'This is a Test' 中字串 'Test' 的區分大小寫搜尋。

USE tempdb;  
GO  
SELECT CHARINDEX ( 'Test',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);

-----------
11

E. 執行不區分大小寫的搜尋

此範例會顯示 'This is a Test' 中字串 'TEST' 的不區分大小寫搜尋。

USE tempdb;  
GO  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CI_AS);  
GO

-----------
11