SQL Server/T-SQL Tutorial/String Functions/CHARINDEX

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

A value of 0 is returned if the string is not found. CHARINDEX does not take into account whether the strings are upper or lower case. 8> DECLARE @STRING_TO_SEARCH_FOR varchar(4) 9> DECLARE @STRING_TO_SEARCH_WITHIN varchar(100) 10> SET @STRING_TO_SEARCH_FOR = "sqle.ru" 11> SET @STRING_TO_SEARCH_WITHIN = "sqle,sqle.ru, www.sqle.ru" 12> SELECT CHARINDEX(@STRING_TO_SEARCH_FOR, @STRING_TO_SEARCH_WITHIN, 6) 13> GO


         8

(1 rows affected)</source>


Finding the Start Position of a String Within Another String

   <source lang="sql">

4> 5> SELECT CHARINDEX("String to Find", "This is the bigger string to find something in.") 6> GO


        20

(1 rows affected) 1></source>


Get index of the delimiting space

   <source lang="sql">

4> DECLARE @FullName VarChar(25), @SpaceIndex TinyInt 5> SET @FullName = "George" 6> 7> -- Get index of the delimiting space: 8> SET @SpaceIndex = CHARINDEX(" ", @FullName) 9></source>


select CHARINDEX("-", "(1212")

   <source lang="sql">

2> select CHARINDEX("-", "(559) 555-1212") 3> GO


        10

(1 rows affected)</source>


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

   <source lang="sql">

4> SELECT CHARINDEX("Mars", "The stars near Mars are far from ours") 5> GO


        16

(1 rows affected) 1></source>


select CHARINDEX("SQL", " SQL Server")

   <source lang="sql">

2> select CHARINDEX("SQL", " SQL Server") 3> GO


         2

(1 rows affected)</source>