SQL Server/T-SQL/String Functions/CHARINDEX
Содержание
- 1 CHARINDEX(): find the first occurrence of a substring within another string
- 2 CHARINDEX: Get index of the delimiting space
- 3 CHARINDEX("Mars", "The stars near Mars are far from ours")
- 4 CHARINDEX: returns the starting point of the first occurrence of one string of characters within another string
- 5 Combine CHARINDEX with substring
CHARINDEX(): find the first occurrence of a substring within another string
<source lang="sql">
1> 2> 3> 4> -- CHARINDEX(): find the first occurrence of a substring within another string. 5> 6> 7> SELECT CHARINDEX("sh", "Washington") 8> GO
3
(1 rows affected) 1> 2>
</source>
CHARINDEX: Get index of the delimiting space
<source lang="sql">
1> DECLARE @FullName VarChar(25), @SpaceIndex TinyInt 2> SET @FullName = "www.sqle.ru" 3> 4> -- Get index of the delimiting space: 5> SET @SpaceIndex = CHARINDEX("java", @FullName) 6> -- Return all characters to the left of the space: 7> SELECT LEFT(@FullName, @SpaceIndex - 1) 8> GO
www. (1 rows affected) 1>
</source>
CHARINDEX("Mars", "The stars near Mars are far from ours")
<source lang="sql">
1> -- 2> 3> SELECT CHARINDEX("Mars", "The stars near Mars are far from ours") 4> 5> GO
16
(1 rows affected) 1>
</source>
CHARINDEX: returns the starting point of the first occurrence of one string of characters within another string
<source lang="sql">
1> -- CHARINDEX: returns the starting point of the first occurrence of one string of characters within 2> -- another string. 3> 4> -- A value of 0 is returned if the string is not found. 5> 6> -- CHARINDEX does not take into account whether the strings are upper or lower case. 7> -- In the example below you would hope that the CHARINDEX function would find the last Wrox, but it actually finds the WROX before that. 8> 9> DECLARE @STRING_TO_SEARCH_FOR varchar(4) 10> DECLARE @STRING_TO_SEARCH_WITHIN varchar(100) 11> 12> SET @STRING_TO_SEARCH_FOR = "a" 13> SET @STRING_TO_SEARCH_WITHIN = "abcdeAabcde" 14> 15> SELECT CHARINDEX(@STRING_TO_SEARCH_FOR, @STRING_TO_SEARCH_WITHIN, 16) 16> 17> GO
0
(1 rows affected) 1>
</source>
Combine CHARINDEX with substring
<source lang="sql">
1> 2> DECLARE @FullName VarChar(25) 3> SET @FullName = "www.sqle.ru" 4> 5> SELECT SUBSTRING(@FullName, 1,CHARINDEX("sqle", @FullName) - 1) 6> GO
www. (1 rows affected) 1>
</source>