SQL Server/T-SQL/String Functions/CHARINDEX

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>