SQL Server/T-SQL/String Functions/CHARINDEX

CHARINDEX(): find the first occurrence of a substring within another string

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>

CHARINDEX: Get index of the delimiting space

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>

CHARINDEX("Mars", "The stars near Mars are far from ours")

1> -- 
2>
3> SELECT CHARINDEX("Mars", "The stars near Mars are far from ours")
4>
5> GO
-----------
         16
(1 rows affected)
1>

CHARINDEX: returns the starting point of the first occurrence of one string of characters within another string

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>

Combine CHARINDEX with substring

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>