в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:19:53Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/String Functions/CHARINDEX - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия