AT() Функция определенная пользователем
Выполняет поиск позиции, с которой n-я подстрока начинается в строке и возвращает номер позиции
AT похожа на функцию INSTR Oracle PL/SQL
AT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
Тип возвращаемого значения smallint
Параметры
@cSearchExpression nvarchar(4000) строка символов, которую функция AT( ) ищет в строке @cExpressionSearched.
@cExpressionSearched nvarchar(4000) строка символов, в которой ищется подстрока @cSearchExpression.
@nOccurrence smallint какое вхождение (первое, второе, третье и так далее)
подстроки @cSearchExpression будет разыскиваться в строке @cExpressionSearched.
По умолчанию, AT() ищет первое вхождение подстроки @cSearchExpression (то есть @nOccurrence = 1).
Указывая параметр @nOccurrence Вы можете искать заданное вхождение подстроки @cSearchExpression
в строке @cExpressionSearched. AT( ) возвращает 0,
если @nOccurrence больше количества вхождений подстроки @cSearchExpression в строке @cExpressionSearched.
Примечания:
AT() возвращает целое число, указывающее на позицию первого символа в строке, в которой осуществлялся поиск.
Если подстрока не найдена, AT() возвращает 0. Поиск, осуществляемый функцией AT() чувствителен к регистру.
AT() осуществляет поиск, учитывая возможные "перекрытия" строк, то есть, например,
подстрока 'ABCA' входит в строку 'ABCABCABCA' три раза
первое вхождение 'ABCA .. BCABCA', второе вхождение 'ABC...ABCA...BCA' , третье вхождение 'ABCABC...ABCA' .
Пример:
declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
select @gcString = N'Никогда не разговаривайте с неизвестными', @gcFindString = N'не'
select dbo.AT(@gcFindString, @gcString, default) -- Возвращает 9
set @gcFindString = N'НЕ'
select dbo.AT(@gcFindString, @gcString, default) -- Возвращает 0, чувствительна к регистру
Также смотрите ATC(), RAT(), AT2(), RATC() функции определенные пользователем
ATC() Функция определенная пользователем
Выполняет поиск позиции, с которой n-я подстрока начинается в строке и возвращает номер позиции
ATC(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
Тип возвращаемого значения smallint
Параметры
@cSearchExpression nvarchar(4000) строка символов которую функция ATC() ищет в строке @cExpressionSearched.
@cExpressionSearched nvarchar(4000) строка символов в которой ищется подстрока @cSearchExpression.
@nOccurrence smallint какое вхождение (первое, второе, третье и так далее)
подстроки @cSearchExpression будет разыскиваться встроке @cExpressionSearched.
По умолчанию, ATC() ищет первое вхождение подстроки @cSearchExpression (то есть @nOccurrence = 1).
Указывая параметр @nOccurrence Вы можете искать заданное вхождение подстроки @cSearchExpression
в строке @cExpressionSearched. ATC() возвращает 0, если @nOccurrence больше количества вхождений
подстроки @cSearchExpression в строке @cExpressionSearched.
Примечания:
ATC() возвращает целое число, указывающее на позицию первого символа в строке, в которой осуществлялся поиск.
Если подстрока не найдена, ATC() возвращает 0. Поиск, осуществляемый функцией ATC() нечувствителен к регистру.
ATC() осуществляет поиск, учитывая возможные "перекрытия" строк, то есть, например,
подстрока 'ABCA' входит в строку 'ABCABCABCA' три раза
первое вхождение 'ABCA .. BCABCA', второе вхождение 'ABC...ABCA...BCA' , третье вхождение 'ABCABC...ABCA' .
Пример:
declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
select @gcString = N'Однажды весною, в час небывало жаркого заката, в Москве, на Патриарших
прудах, появились два гражданина.', @gcFindString = 'на'
select dbo.ATC(@gcFindString, @gcString, 2) -- Возвращает 59
set @gcFindString = 'НА'
select dbo.ATC(@gcFindString, @gcString, 3) -- Возвращает 105, нечувствительна к регистру
Также смотрите AT(), RAT(), AT2(), RATC() функции определенные пользователем
AT2() Функция определенная пользователем
Выполняет поиск позиции, с которой n-я подстрока начинается в строке и возвращает номер позиции
AT2 похожа на функцию INSTR Oracle PL/SQL
AT2(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
Тип возвращаемого значения smallint
Совпадает с функцией AT(), за исключением того, что
AT2() осуществляет поиск, НЕ учитывая возможные "перекрытия" строк, то есть, например,
подстрока 'ABCA' входит в строку 'ABCABCABCA' два раза
первое вхождение 'ABCA .. BCABCA', второе вхождение 'ABCABC...ABCA' .
RAT( ) Функция определенная пользователем
Выполняет поиск позиции, с которой n-я подстрока начинается в строке и возвращает номер позиции,
но в отличии от AT() осуществляет поиск справа налево.
RAT(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
Тип возвращаемого значения smallint
Параметры
@cSearchExpression nvarchar(4000) искомая строка.
@cExpressionSearched nvarchar(4000) строка, в которой функция RAT( )
будет искать строку @ cSearchExpression. @nOccurrence smallint какое вхождение,
отсчитывая справа налево, подстроки @cSearchExpression в строке @cExpressionSearched
будет разыскиваться функцией RAT(). По умолчанию, RAT() ищет последнее вхождение подстроки
@cSearchExpression в строке @cExpressionSearched (то есть @nOccurrence = 1).
Если @nOccurrence равно 2, RAT() ищет предпоследнее вхождение и так далее.
Примечания:
RAT() возвращает целое число, указывающее на позицию первого символа в строке,
в которой осуществлялся поиск. RAT() возвращает 0, если строка @cSearchExpression не найдена
в строке @cExpressionSearched, либо если @nOccurrence больше количества вхождений подстроки
@cSearchExpression в строке @cExpressionSearched. Поиск, осуществляемый функцией RAT(),
чувствителен к регистру. RAT() осуществляет поиск, учитывая возможные "перекрытия" строк,
то есть, например, подстрока 'ABCA' входит в строку 'ABCABCABCA' три раза
первое вхождение (справа налево) 'ABCABC...ABCA', второе вхождение 'ABC...ABCA...BCA' ,
третье вхождение 'ABCA .. BCABCA'.
Пример:
declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
select @gcString = N'Коровьев против Панаева написал "Скабичевский", а Бегемот
против Скабичевского написал "Панаев". ', @gcFindString = N'Панаев'
select dbo.RAT(@gcFindString , @gcString, default) -- Возвращает 91
select dbo.RAT(@gcFindString , @gcString , 2) -- Возвращает 17
Также смотрите AT(), ATC(), AT2(), RATC() функции определенные пользователем
RATC( ) Функция определенная пользователем
Выполняет поиск позиции, с которой n-я подстрока начинается в строке и возвращает номер позиции,
но в отличии от AT() осуществляет поиск справа налево.
RATC(@cSearchExpression, @cExpressionSearched [, @nOccurrence])
Тип возвращаемого значения smallint
Совпадает с функцией RAT(), за исключением того, что поиск, осуществляемый функцией RATC(),
НЕчувствителен к регистру.
Также смотрите AT(), ATC(), AT2(), RAT() функции определенные пользователем
OCCURS() Функция определенная пользователем
Возвращает количество вхождений подстроки в строке (учитывает "перекрытия" подстрок).
OCCURS(@cSearchExpression, @cExpressionSearched)
Тип возвращаемого значения smallint
Параметры
@cSearchExpression nvarchar(4000) подстрока, которую функция OCCURS() будет искать в строке @cExpressionSearched.
@cExpressionSearched nvarchar(4000) строка в которой функция OCCURS() будет искать подстроку @cSearchExpression.
Примечания:
OCCURS() возвращает 0 (нуль) если подстрока @cSearchExpression не найдена в строке @cExpressionSearched.
Пример:
declare @gcString nvarchar(4000)
select @gcString = N'Мастер и Маргарита'
select dbo.OCCURS(N'а', @gcString ) -- Возвращает 4
select dbo.OCCURS(N'р', @gcString ) -- Возвращает 3
select dbo.OCCURS(N'и', @gcString ) -- Возвращает 2
select dbo.OCCURS(N'т', @gcString ) -- Возвращает 2
Внимание, учитывает "перекрытия" символов !!!
select dbo.OCCURS('ABCA', 'ABCABCABCA') -- Возвращает 3
первое вхождение 'ABCA .. BCABCA'
второе вхождение 'ABC...ABCA...BCA'
третье вхождение 'ABCABC...ABCA'
Также смотрите AT(), RAT(), OCCURS2()
OCCURS2() Функция определенная пользователем Автор: Stephen Dobson, Торонто
Возвращает количество вхождений подстроки в строке (не учитывает "перекрытия" подстрок).
OCCURS2(@cSearchExpression, @cExpressionSearched)
Тип возвращаемого значения smallint
Параметры
@cSearchExpression nvarchar(4000) подстрока, которую функция OCCURS2() будет искать в строке @cExpressionSearched.
@cExpressionSearched nvarchar(4000) строка в которой функция OCCURS2() будет искать подстроку @cSearchExpression.
Примечания:
OCCURS2() возвращает 0 (нуль) если подстрока @cSearchExpression не найдена в строке @cExpressionSearched.
Пример: 1
declare @gcString nvarchar(4000)
select @gcString = 'abracadabra'
select dbo.OCCURS2('a', @gcString ) -- Возвращает 5
select dbo.OCCURS2('b', @gcString ) -- Возвращает 2
Пример: 2
-- Подсчитывает количество различных символов из строки @gcCaracters содержащихся в строке @gcString.
declare @gcString nvarchar(4000), @gcCaracters nvarchar(256), @i smallint, @counter smallint
select @i = 1, @counter = 0
select @gcString = N'" Остальные четыре охотника с большим интересом прислушивались к спору,
-- кто лежа на койке, кто приподнявшись и облокотясь на стол, -- и временами
подавали реплики. Иногда они начинали говорить все сразу, и тогда в тесном
кубрике голоса их звучали подобно раскатам бутафорского грома. Они спорили о
пустяках, как дети, и доводы их были крайне наивны. Собственно говоря, они
даже не приводили никаких доводов, а ограничивались голословными
утверждениями или отрицаниями. Умение или неумение новорожденного тюленя
плавать они пытались доказать просто тем, что высказывали свое мнение с
воинственным видом и сопровождали его выпадами против национальности,
здравого смысла или прошлого своего противника. Я рассказываю об этом, чтобы
показать умственный уровень людей, с которыми принужден был общаться.
Интеллектуально они были детьми, хотя и в обличье взрослых мужчин." Джек Лондон. Морской Волк',
@gcCaracters = N'абссд'
while @i <= datalength(@gcCaracters)/2
begin
if charindex(substring(@gcCaracters,@i,1), left(@gcCaracters, @i - 1)) = 0
select @counter = @counter + dbo.OCCURS2(substring(@gcCaracters,@i,1), @gcString)
select @i = @i + 1
end
select @counter -- Возвращает 127
Внимание, функция OCCURS2() НЕ учитывает "перекрытия" символов !!!
select dbo.OCCURS2('ABCA', 'ABCABCABCA') -- Возвращает 2
первое вхождение 'ABCA .. BCABCA'
второе вхождение 'ABCABC... ABCA'
Также смотрите OCCURS()
PADL(), PADR(), PADC() Функции определенные пользователем
Возвращает в виде символьной строки, заданную первым аргументом строку, дополненную третьим аргументом до длины,
заданной вторым аргументом, причем вставляет символы-заполнители слева, справа или с обеих сторон.
PADL(@eExpression, @nResultSize [, @cPadCharacter]) или
PADR(@eExpression, @nResultSize [, @cPadCharacter]) или
PADC(@eExpression, @nResultSize [, @cPadCharacter])
Тип возвращаемого значения nvarchar(4000)
Параметры
@eExpression nvarchar(4000) строка символов, которая будет дополнена строкой(ами) @cPadCharacter.
@nResultSize smallint общая длина строки @eExpression после дополнения строкой(ами) @cPadCharacter.
@cPadCharacter nvarchar(4000) строка символов, которая будет использована для дополнения строки @eExpression.
Если длина выражения больше @nResultSize, выражение усекается справа до заданной величины.
Если задан третий аргумент cPadCharacter, то его повторения используются для дополнения
исходного выражения до требуемой длины , иначе, по умолчанию, @cPadCharacter является пробелом.
Примечания:
PADL() вставляет строки-заполнители слева, PADR() справа, PADC() с обеих сторон.
Функции PADL, PADR похожи на функции Oracle PL/SQL LPAD, RPAD.
Пример:
select @gcString = N'Воланд'
select dbo.PADL(@gcString, 25, default) -- Возвращает ' Воланд'
select dbo.PADL(@gcString, 25, '+=+') -- Возвращает '+=++=++=++=++=++=++Воланд'
select dbo.PADR(@gcString, 25, '=!!!=') -- Возвращает 'Воланд=!!!==!!!==!!!==!!!'
select dbo.PADC(@gcString, 25, '=*=') -- Возвращает '=*==*==*=Воланд=*==*==*=='
CHRTRAN() Функция определенная пользователем
Выполняет перекодировку выражения, заданного в первой строке, используя вторую и третью строки
в качестве таблицы перекодировки.
CHRTRAN похожа на функцию TRANSLATE Oracle PL/SQL.
CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
Тип возвращаемого значения nvarchar (4000)
Параметры
@cSearchedExpression исходная строка, в которой функция CHRTRAN() будет заменять символы.
@cSearchExpression символы из данной строки будут заменяться в строке @cSearchedExpression.
@cReplacementExpression на данные символы будут заменены символы из строки @cSearchedExpression.
Все позиции, занимаемые в @cSearchedExpression первым символом из @cSearchExpression заменяются
первым символом из @cReplacementExpression, второй символ из @cSearchExpression заменяется
в @cSearchedExpression вторым символом из @cReplacementExpression и так далее.
Если какой либо символ из строки @cSearchExpression не найден в строке @cSearchedExpression,
то с данным символом ничего не происходит.
Примечания:
Если @cReplacementExpression содержит меньше символов, чем @cSearchExpression,
то "лишние" символы из @cSearchExpression заменяются в @cSearchedExpression
пустыми символами, то есть удаляются. Если @cReplacementExpression содержит больше символов,
чем @cSearchExpression, то "лишние" символы в @cReplacementExpression игнорируются.
Пример:
select dbo.CHRTRAN(N'Мастер и.', N'стер и.', N'ргарита') -- Возвращает 'Маргарита'
select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Возвращает 'XBYDZF'
select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Возвращает 'XBYDZF'
Также смотрите STRFILTER()
STRTRAN() Функция определенная пользователем
Возвращает строку символов, полученную из первой строки, в которой одно или несколько вхождений
второй подстроки заменены третьей подстрокой (в отличии от встроенной функции replace,
STRTRAN имеет три дополнительных параметра)
STRTRAN (@cSearched, @cExpressionSought , [@cReplacement][, @nStartOccurrence]
[, @nNumberOfOccurrences] [, @nFlags])
Тип возвращаемого значения nvarchar(4000)
Параметры
@cSearched исходная строка, в которой функция STRTRAN()
будет заменять найденные подстроки @cExpressionSought.
@cExpressionSought подстрока, которая будет разыскиваться и заменяться в @cSearched.
@cReplacement подстрока, служащая для замены @cExpressionSought в строке @cSearched.
Если подстрока @cReplacement не указана, каждое вхождение подстроки @cExpressionSought
будет заменено на пустую строку.
@nStartOccurrence с какого вхождения подстроки @cExpressionSought будет начата замена.
Например, если @nStartOccurrence равно 4, замена начнется с четвертого вхождения подстроки
@cExpressionSought в строке @cSearched, а первые три вхождения подстроки @cExpressionSought
останутся на своем месте.
По умолчанию замена начинается с первого вхождения подстроки @cExpressionSought,
если параметр @nStartOccurrence не был указан.
@nNumberOfOccurrences определяет количество вхождений подстроки @cExpressionSought
в строке @cSearched которое будет заменено. Если параметр @nNumberOfOccurrences не был указан,
то все вхождения подстроки @cExpressionSought в строке @cSearched,
начиная с вхождения @nStartOccurrence будут заменены.
@nFlags Определяет поиск и замену в соответствии со следующими значениями :
-----------------------------------------------------------------------------------------
@nFlags Описание
0 (по умолчанию) Поиск чувствителен к регистру, найденная подстрока заменяется на подстроку @cReplacement.
1 Поиск НЕ чувствителен к регистру, найденная подстрока заменяется на подстроку @cReplacement.
2 Поиск чувствителен к регистру, регистр буквенных символов в подстроке замены @cReplacement
будет изменен в соответствии с регистром буквенных символов найденной подстроки.
Регистр буквенных символов подстроки @cReplacement меняется только в случае, если найденная подстрока
соответствует верхнему регистру, нижнему регистру или регистру заголовка
(то есть первая буква найденной подстроки прописная, а остальные строчные).
3 Поиск НЕ чувствителен к регистру, регистр буквенных символов в подстроке замены @cReplacement
будет изменен в соответствии с регистром буквенных символов найденной подстроки. Регистр буквенных
символов подстроки @cReplacement меняется только в случае, если найденная подстрока соответствует
верхнему регистру, нижнему регистру или регистру заголовка
(то есть первая буква найденной подстроки прописная, а остальные строчные).
-----------------------------------------------------------------------------------------
Примечания:
Замена начинается с @nStartOccurrence -ой по счету реализации и производится @nNumberOfOccurrences раз.
Если @cReplacement отсутствует, @cExpressionSought заменяется пустой строкой (удаляется).
Если не задано @nStartOccurrence, подразумевается замена с первой реализации.
Отсутствие @nNumberOfOccurrences подразумевает замену всех реализаций.
Передайте -1 для необязательных парметров, если нужно всего лишь передать параметр @nFlags.
Пример:
select dbo.STRTRAN('ABCDEF', 'ABC', 'XYZ',-1,-1,0) -- Возвращает XYZDEF
select dbo.STRTRAN('ABCDEF', 'ABC', default,-1,-1,0) -- Возвращает DEF
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', default,2,-1,0) -- Возвращает ABCDEFGHJabcQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', default,2,-1,1) -- Возвращает ABCDEFGHJQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 1) -- Возвращает ABCDEFXYZGHJabcQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 1) -- Возвращает ABCDEFXYZGHJXYZQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 1, 2) -- Возвращает ABCDEFXYZGHJabcQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'XYZ', 2, 3, 2) -- Возвращает ABCDEFXYZGHJabcQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xyZ', 2, 1, 2) -- Возвращает ABCDEFXYZGHJabcQWE
select dbo.STRTRAN('ABCDEFABCGHJabcQWE', 'ABC', 'xYz', 2, 3, 2) -- Возвращает ABCDEFXYZGHJabcQWE
select dbo.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 2) -- Возвращает ABCDEFAbcCGHJAbcQWE
select dbo.STRTRAN('abcDEFabcGHJabcQWE', 'abc', 'xYz', 2, 3, 2) -- Возвращает abcDEFxyzGHJxyzQWE
select dbo.STRTRAN('ABCDEFAbcCGHJAbcQWE', 'Aab', 'xyZ', 2, 1, 3) -- Возвращает ABCDEFAbcCGHJAbcQWE
select dbo.STRTRAN('ABCDEFAbcGHJabcQWE', 'abc', 'xYz', 1, 3, 3) -- Возвращает XYZDEFXyzGHJxyzQWE
Также смотрите replace(), CHRTRAN()
STRFILTER() Функция определенная пользователем
Удаляет все символы из первой строки, за исключением символов, содержащихся во второй строке
STRFILTER(@cExpressionSearched, @cSearchExpression)
Тип возвращаемого значения nvarchar(4000)
Параметры
@cExpressionSearched строка из которой будут удалены те символы, которых нет в строке @cSearchExpression.
@cSearchExpression определяет те символы, которые нужно оставить в строке @cExpressionSearched.
Примечания:
STRFILTER() удаляет из @cExpressionSearched те символы, которых нет в @cSearchExpression,
затем возвращает то, что осталось.
Пример:
select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Возвращает 516378306
select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Возвращает ABABAB
Также смотрите CHRTRAN() функция определенная пользователем
GETWORDCOUNT() Функция определенная пользователем
Возвращает количество слов в строке.
GETWORDCOUNT(@cString[, @cDelimiters])
Тип возвращаемого значения smallint
Параметры
@cString nvarchar(4000) - строка, количество слов в которой будет подсчитано.
@cDelimiters nvarchar(256) - необязательный параметр, определяет те символы,
которые будут служить разделителями слов в строке @cString. Разделителями по умолчанию являются пробелы,
символы табуляции, возврата каретки и перевода строки.
GETWORDCOUNT () считает каждый символ из строки @cDelimiters как отдельный разделитель,
а не всю строку @cDelimiters как единый разделитель.
Примечания:
GETWORDCOUNT () по умолчанию считает, что слова разделены пробелами, символами табуляции,
возврата каретки и перевода строки. Если передан параметр @cDelimiters,
функция игнорирует пробелы, символы табуляции, возврата каретки и перевода строки.
Пример:
declare @cString nvarchar(4000)
set @cString = N'Это водка? -- слабо спросила Маргарита.
Кот подпрыгнул на стуле от обиды.
Помилуйте, королева, - прохрипел он, разве я позволил бы себе налить даме водки? Это чистый спирт!'
select dbo.GETWORDCOUNT(@cString, default) -- Возвращает 28 , группа символов разделенных пробелом
select dbo.GETWORDCOUNT(@cString, ',') -- Возвращает 4 - группа символов разделенных запятой
Также смотрите GETWORDNUM(), GETALLWORDS(), GETALLWORDS2() функции определенные пользователем
GETWORDNUM() Функция определенная пользователем
Возвращает заданное слово из строки.
GETWORDNUM(@cString, @nIndex[, @cDelimiters])
Тип возвращаемого значения nvarchar(4000)
Параметры
@cString nvarchar(4000) - строка, слово номер @nIndex в которой будет возвращено.
@nIndex smallint - определяет номер слова, которое будет возвращено. Например, если @nIndex равно 3,
функция GETWORDNUM() возвратит третье слово (если @cString содержит три или более слов).
@cDelimiters nvarchar(256) - необязательный параметр, определяет те символы, которые будут служить
разделителями слов в строке @cString. Разделителями по умолчанию являются пробелы, символы табуляции,
возврата каретки и перевода строки.
GETWORDNUM () считает каждый символ из строки @cDelimiters как отдельный разделитель,
а не всю строку @cDelimiters как единый разделитель.
Примечания:
Функция GETWORDNUM () по умолчанию считает, что слова разделены пробелами, символами табуляции,
возврата каретки и перевода строки. Если передан параметр @cDelimiters, функция игнорирует пробелы,
символы табуляции, возврата каретки и перевода строки.
Функция GETWORDNUM() возвращает слово номер @nIndex из строки @cString.
Если @cString содержит меньше чем @nIndex слов, функция GETWORDNUM() возвращает пустую строку.
Пример:
declare @cString nvarchar(4000)
set @cString = N'Не желала бы я встретиться с вами, когда у вас в руках револьвер,
кокетливо поглядывая на Азазелло, сказала Маргарита. У нее была страсть ко всем людям,
которые делают что-либо первоклассно.'
select dbo.GETWORDNUM(@cString, 17, default) -- Возвращает 'Азазелло,'
Также смотрите GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() функции определенные пользователем
GETALLWORDS() Функция определенная пользователем
Помещает в таблицу все слова из строки
GETALLWORDS(@cString[, @cDelimiters])
Тип возвращаемого значения таблица @GETALLWORDS (WORDNUM smallint, WORD nvarchar(4000),
STARTOFWORD smallint, LENGTHOFWORD smallint)
Описание структуры возвращаемой таблицы @GETALLWORDS
поле WORDNUM smallint - порядковый номер слова в строке @cString
поле WORD nvarchar(4000) - слово
поле STARTOFWORD smallint - позиция в строке @cString, с которой начинается слово
поле LENGTHOFWORD smallint - длина слова
Параметры
@cString nvarchar(4000) - строка, слова которой будут помещены в таблицу @GETALLWORDS.
@cDelimiters nvarchar(256) - необязательный параметр, определяет те символы, которые будут служить
разделителями слов в строке @cString. Разделителями по умолчанию являются пробелы, символы табуляции,
возврата каретки и перевода строки.
GETALLWORDS() считает каждый символ из строки @cDelimiters как отдельный разделитель,
а не всю строку @cDelimiters как единый разделитель.
Примечания: GETALLWORDS() по умолчанию считает, что слова разделены пробелами,
символами табуляции, возврата каретки и перевода строки. Если передан параметр @cDelimiters,
функция игнорирует пробелы, символы табуляции, возврата каретки и перевода строки.
Пример:
declare @cString nvarchar(4000)
set @cString = 'Как прикажете, мессир? - спросил Фагот у замаскированного.
Ну что же, - задумчиво отозвался тот, они люди как люди.
Любят деньги, но ведь это всегда было... Человечество любит деньги, из чего
бы те ни были сделаны, из кожи ли, из бумаги ли, из бронзы или из золота.
Ну, легкомысленны... ну, что ж... и милосердие иногда стучится в их
сердца... обыкновенные люди... в общем, напоминают прежних... квартирный
вопрос только испортил их...'
select * from dbo.GETALLWORDS(@cString, default)
select * from dbo.GETALLWORDS(@cString, ' ,.')
Также смотрите GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS2() функции определенные пользователем
GETALLWORDS2() Функция определенная пользователем
Помещает в таблицу все слова из строки (но в отличии от функции GETALLWORDS() считает всю строку
@cStringSplitting как единый разделитель слов).
GETALLWORDS2(@cString[, @cStringSplitting])
Тип возвращаемого значения таблица @GETALLWORDS2 (WORDNUM smallint, WORD nvarchar(4000),
STARTOFWORD smallint, LENGTHOFWORD smallint)
Описание структуры возвращаемой таблицы @GETALLWORDS
поле WORDNUM smallint - порядковый номер слова в строке @cString
поле WORD nvarchar(4000) - слово
поле STARTOFWORD smallint - позиция в строке @cString, с которой начинается слово
поле LENGTHOFWORD smallint - длина слова
Параметры
@cString nvarchar(4000) - строка, слова которой будут помещены в таблицу @GETALLWORDS2.
@cStringSplitting nvarchar(256) - необязательный параметр, определяет строку, которая будет служить
разделителями слов в строке @cString. Разделителем по умолчанию является пробел.
GETALLWORDS2() считает всю строку @cStringSplitting как единый разделитель.
Примечания: GETALLWORDS2() по умолчанию считает, что слова разделены пробелами.
Если передан параметр @cStringSplitting, функция игнорирует пробелы,
а использует строку @cStringSplitting как единый разделитель.
Пример:
declare @cString nvarchar(4000), @nIndex smallint
select @cString = 'Увы, не нам, не нам, - грустно заговорил Коровьев,
а ему достанется эта ледяная кружка пива, о которой мы, бедные скитальцы, так мечтали с тобой,
положение наше печально и затруднительно, и я не знаю, как быть.', @nIndex = 17
-- возвращает заданное слово из строки @cString (разделителем слов является пробел).
select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex -- Возвращает 'пива,'
-- возвращает количество слов в строке @cString (разделителем слов является пробел).
select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc -- Возвращает 37
Также смотрите GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS() функции определенные пользователем
PROPER( ) Функция определенная пользователем
Возвращает строку, преобразуя в ней первую букву каждого слова в прописную, а остальные в строчные.
PROPER похожа на функцию INITCAP Oracle PL/SQL
PROPER(@cExpression)
Тип возвращаемого значения nvarchar(4000)
Параметры
@cExpression nvarchar(4000) Строка в которой функция PROPER( ) преобразует первую букву каждого слова
в прописную, а остальные в строчные.
Пример:
select dbo.PROPER(N'МИХАИЛ АФАНАСЬЕВИЧ БУЛГАКОВ (1891-1940)')
-- Возвращает 'Михаил Афанасьевич Булгаков (1891-1940)'
ARABTOROMAN() Возвращает римское число в виде символьного выражения соответствующее переданному числу
ARABTOROMAN(@tNum) Тип возвращаемого значения varchar(15)
Параметры @tNum number
Пример:
select dbo.ARABTOROMAN(3888) -- Возвращает MMMDCCCLXXXVIII
ROMANTOARAB() Возвращает число соответствующее римскому числу
ROMANTOARAB(@tcRomanNumber) Тип возвращаемого значения smallint
Параметры @tcRomanNumber varchar(15) римское число
Пример:
select dbo.ROMANTOARAB('MDCCCLXXXVIII') -- Возвращает 1888
-- баг функции charindex
-- этот баг есть и в MS SQL 2005 Yukon и в MS SQL 2000
declare @cSearchExpression nvarchar(10), @cExpressionSearched nvarchar(50)
select @cSearchExpression = 'Q'+char(0), @cExpressionSearched = 'Q'+char(0)+'NZQ'+char(0)+'Q'
select charindex(@cSearchExpression COLLATE Latin1_General_BIN, @cExpressionSearched) -- возвращает 1
select charindex(@cSearchExpression COLLATE Latin1_General_CI_AS, @cExpressionSearched) -- возвращает 5,
но это неправильный результат, правильный результат 1
-- баг функции replace
-- этот баг исправлен в SQL 2005
-- выполните следующий пример
declare @n nvarchar(4000)
set @n = replicate(N'z',3999)+N'i'
select right(replace(@n, N'i', N'B'),1)
-- возвращает i, но это неправильный результат, правильный результат это B
-- моя информация
SELECT SERVERPROPERTY('Edition') -- возвращает Desktop Engine
SELECT SERVERPROPERTY('ProductLevel') -- возвращает SP4
SELECT SERVERPROPERTY('ProductVersion') -- возвращает 8.00.2039
Динамический запрос, возвращающий параметры, к сожалению, явно в документации не описан.
А) Простой пример динамического запроса, возвращающего параметр
-- Текст динамического запроса и строка определения параметров обязательно должны
-- быть в переменной типа nvarchar, nchar или ntext
declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000)
-- c третьим параметром, передаваемом в динамический запрос как выходной, возвращается длина
-- гипотенузы прямоугольного треугольника, первые два параметра это длины катетов
declare @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- вызываем динамический запрос таким способом
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0,
@nHypotenuse = @nHypotenuse out
-- либо таким способом
exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out
select @nHypotenuse -- Displays 5.0
Б) Пример использования динамического запроса, возвращающего параметр, и функции GETALLWORDS
Следующая хранимая процедура "вытаскивает" все слова из поля типа text или ntext,
длина слова не должна превышать 4000 символов.
-- создаем хранимую процедуру
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT
@TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname,
@cDelimiters nvarchar(256) = NULL
AS
-- хранимая процедура возвращает из текстового поля содержащиеся в нем слова
-- и помещает их в таблицу следующей структуры
-- WORDNUM int - порядковый номер слова
-- WORD nvarchar(4000) - слово
-- STARTOFWORD int - позиция в текстовом поле, с которой начинается слово
-- LENGTHOFWORD smallint - длина слова
-- параметры
-- @TableName имя таблицы, которая имеет поле типа text или ntext
-- @FieldIdName имя Id поля
-- @FieldIdValue значение Id поля, соответствующее текстовому полю
-- @FieldTextName имя поля типа text или ntext
-- @cDelimiters строка из символов разделителей
begin
set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int,
@nEndString int, @divisor tinyint, @flag bit, @RetTable bit,
@cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000),
@ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint
select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))),
@FieldIdName = lower(ltrim(rtrim(@FieldIdName))),
@FieldTextName = lower(ltrim(rtrim(@FieldTextName))),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)),
-- Разделителями по умолчанию являются пробелы, символы табуляции, возврата каретки и перевода строки..
@nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- если временная таблица не определена в вызывающей процедуре, создаем временную таблицу
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null
begin
create table #GETALLWORDSFROMTEXT
(WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
select @RetTable = 1
end
-- используем динамический запрос для того, чтобы получить
-- точное имя текстового поля (символы в нужном регистре),
-- поскольку мы можем написать имена полей при вызове данной хранимой процедуры
-- в произвольном регистре
-- мы получаем имя текстового поля из динамически вызываемого запроса
-- для этого в строке определения параметров @ParmDefinition указываем ключевое слово output
-- и при вызове динамического запроса exec sp_executesql @SQLString, @ParmDefinition,
-- @FieldTextName = @FieldTextName output
-- также указываем ключевое слово output для определенного ранее параметра
select @SQLString = 'select @FieldTextName = name from syscolumns
where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
select @ParmDefinition = '@FieldTextName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
--используем динамический запрос, для того чтобы получить точное имя Id поля
select @SQLString = 'select @FieldIdName = name from syscolumns where
id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
select @ParmDefinition = '@FieldIdName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
--используем динамический запрос для того, чтобы получить тип текствого поля text или ntext
select @SQLString = 'select @TypeField = name from systypes where xtype =
any ( select xtype from syscolumns where id
= OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
select @ParmDefinition = '@TypeField varchar(13) output'
exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
--используем динамический запрос для того, чтобы получить длину текствого поля
select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+
cast( @divisor as nchar(1)) +
' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@nEndString int output'
exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- "нарезаем" текстовое поле на подстроки длиной не более 4000 символов
-- и работаем с подстроками в цикле
while 1 > 0
begin
-- используем динамический запрос для того,
-- чтобы получить подстроку типа nvarchar(4000) из текствого поля
select @SQLString = 'select @cString =
substring('+ @FieldTextName+',' +
cast( @nBegSubString as nvarchar(20)) +',' +
cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+')
from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@cString nvarchar(4000) output'
exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
select @nBegSubString1 = 1,
@nEndSubString1 = @nEndSubString - @nBegSubString +1
-- пока не нашли начало слова
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN,
@cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString
select @nBegSubString = @nBegSubString + 1,
@nBegSubString1 = @nBegSubString1 + 1
-- пока не нашли разделитель слов
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN,
@cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString
select @nEndSubString = @nEndSubString - 1,
@nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString
begin
select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT
order by WORDNUM desc
select @cString = substring(@cString, @nBegSubString1,
@nEndSubString1-@nBegSubString1+1)
-- используем функцию GETALLWORDS
-- которая работает со строками типа nvarchar(4000)
-- добавляем результат во временную таблицу
insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD)
select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1),
LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1,
@nEndSubString = @nEndSubString + 4000
end
else
select @nEndSubString = @nEndSubString + 4000
-- в случае если вся подстрока состоит из одних разделителей
if @flag = 1
break
if @nEndString <= @nEndSubString
select @flag = 1, @nEndSubString = @nEndString
end
-- если в вызывающей процедуре таблица не была определена показываем результат
if @RetTable = 1
select * from #GETALLWORDSFROMTEXT
end
GO
Пример вызова хранимой процедуры
declare @cDelimiters nvarchar(256)
select @cDelimiters = '"-,.:!?""()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12)
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
drop table #GETALLWORDSFROMTEXT
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000),
STARTOFWORD int, LENGTHOFWORD smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'имя Вашей таблицы', 'имя Вашего Id поля',
фактическое значение Id поля , 'имя поля типа text или ntext', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
select * from #GETALLWORDSFROMTEXT
Сравнение space(2) = space(1) возвращает TRUE
if space(2) = space(1)
select ' space(2) = space(1) '
но select charindex(space(2),space(1),1) -- возвращает 0
то есть имеется некоторое противоречие.
Особенность работы функции substring Transact-SQL.
если substring ('tra-la-la', 0, 2) предыдущих версий MS SQL Server возвращает пустую строку,
то есть при втором аргументе меньше единицы всегда возвращается пустая строка, то
функция substring('tra-la-la', 0, 2) MS SQL Server 2000 возвращает 't',
то есть при втором аргументе меньше единицы
нужно смотреть на третий аргумент, и в зависимости от него будет получен результат,
скажем, substring('tra-la-la', -5, 8) функция возвращает 'tr' .
Объяснение этому здесь http://support.microsoft.com/kb/q310421/
Вопросы, скептические замечания и мои ответы на них:
"Transact-SQL тоже язык не бедный и к чему изобретать велосипед в каждом языке родное надежнее…
На мой взгляд, лучше просто изучить подробнее стандартные функции Transact-SQL,
там должно быть всИ, ну, или почти все"
"Лично я стараюсь не использовать пользовательские функции в MS SQL Server...
В принципе, пока обходился стандартными... А там уже не за горами версия от 2005 года..."
Если бы существовала острая необходимость в написанных мною функциях Transact-SQL,
их написал бы не я, а разработчики SQL Server.
Острой необходимости в них нет, каждый день происходит 20-30 закачек, хотя объявления представлены
в разделах скриптов на достаточно популярных форумах.
В мире сотни тысячи пользователей SQL Server, закачали же мои функции за полгода
со всех источников более 7000.
Я встретил поначалу массу скептических отзывов со стороны многих MVP,
встретил, правда и положительные отзывы со стороны тех же MVP,
в общем, мнения разделились. Ну а тот, кому надо, берет и использует, просто многие из тех,
кому они действительно могут быть полезными, не знают и не подозревают об их существовании.
Функций GETWORDCOUNT, GETWORDNUM, AT, RAT, CHRTRAN, STRFILTER, OCCURS, PADC, PADR, PADL, PROPER
(схожих не по имени, а по функциональности) в Transact-SQL MS SQl Server 2000 и в 2005 нет.
"родное надежнее"
Смотрите "баг встроенных функций replace и charindex".
Именно по причине большого количества дешевых поделок, умные люди не используют "функции",
"библиотеки" в большом количестве встречающиеся в Интернете. Начинаешь что-нибудь ставить,
а оно не устанавливается, как можно этому доверять. Как можно доверять серьезные
приложения функциям написанным неизвестно кем ?
Поэтому со скептицизмом встречают и вышеприведенные функции, но они потихоньку находят
своих пользователей, после публикации на серьезных форумах количество скептиков сошло на нет,
по существу возразить никто не может. Код открыт и всегда можно убедится в его корректности.
Функции работают и со строками в UNICODE, то есть к примеру с символами китайского языка. :-)
"Может пример приведете из своего опыта, когда что-то сделать невозможно при работе со строками"
Два примера, скорее обратных, показывающих, что со строками можно сделать все,
используя встроенные функции, но не из моего опыта, поскольку, если задача встречается
часто или может в будущем встретится часто, я предпочитаю писать процедуру или функцию.
Первый пример:
На microsoft.public.sqlserver.programming некто Dave 24 апреля 2005 года спрашивает
(ему видимо лень, было, обходится встроенными функциями или заходить на www.google.com набирать
в строке поиска User-Defined string functions Transact-SQL)
How can I use the T-SQL string functions to find the _third_ occourance of a
character.
For example, I have an email field and it has data that looks like this:
http://www.sqlservercentral.com/scripts/contributions/1488.asp
I want to find the position of the third occurence of '/' so I can use its
position to extract just the main domain name, truncating all the trailing
garbage.
Does anyone have any ideas on how to do this?
Ему с готовностью помогают, демонстрируя возможность обойтись встроенными функциями
(действительно, можно, нет никаких сомнений)
____________________________________________________________
Dave,
Here's an example that should help you out:
declare @t table (
s nvarchar(1000) )
insert into @t values ('http://www.sqlservercentral.com/scripts/contributions/1488.asp')
insert into @t values
('http://www.sqlservercentral.com/scripts/contributions/1488.asp')
select
substring(s,a,charindex('/',s,a)-a) as theDomain
from (
select
s,
charindex('//',s) + 2 as a
from @t
)
Steve Kass
Drew University
____________________________________________________________
Далее Dave благодарит за ценный совет, но получает еще один
____________________________________________________________
This should extract the Domain Name...
Declare @V VarChar(89)
Set @V = 'http://www.sqlservercentral.com/scripts/contributions/1488.asp'
Select Substring(@V, CharIndex('//', @V) + 2, CharIndex('/',
Substring(@V, CharIndex('//', @V) + 2, Len(@V)))-1)
____________________________________________________________
Ну и, естественно, я вставил свои три копейки
select dbo.AT('/','http://www.sqlservercentral.com/scripts/contributions/1488.asp',3)
Видимо, на нашей планете, можно найти сотни тысяч подобных строк кода
в базах SQL Server , иначе бы мне не говорили спасибо на нескольких языках,
многие ведь нашли решение сами, не спрашивая на форумах.
Второй пример:
На http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49733
некто JAme задает вопрос
hi,
is there a similar function in SQL server to ORACLES RPAD ?
I need to display a field for a fixed width of 10.
if the field is not 60 then add blank spaces . .
eg:
[jamie ]
[sqlteam ]
and so on . .. .
thank you for any help/
JAme
____________________________________________________________
То есть интересуется - имеется ли нечто подобное функции RPAD PL/SQL.
Да, имеется функция PADR, если бы он поискал на форуме, то нашел бы, поскольку мое сообщение
было опубликовано несколькими днями ранее на том же форуме.
robvolk, SQL Server MVP and SQLTeam MVY и прочая и прочая (всех титулов и званий достаточно много)
начинает давать дельные советы, что то про bcp и т.д. и т.п., разворачивается оживленная дискуссия,
его благодарят и т.д. и т.п.
В общем, кому интересно, можете почитать.
____________________________________________________________
"Ну а особенности с ними нужно дружить, а не воевать"
Их нужно сначала знать, для этого и существуют форумы, где обмениваются информацией,
Форумы ведь существуют не только для того чтобы отвечать на вопросы Dave ов.
У меня ушло некоторое время на то, чтобы разобраться с поведением функции Substring.
Ну и в заключение, привожу фрагмент письма.
"To Igor Nikiforov,
Thank you for sharing your code. I Googled 'SQL count occurrence of
character in a string' and bingo! Didn't have to write it and got so
much more.
Appreciate it.
Phil Youker
Programmer/Analyst
VCSSO/Information Technology Services"
|
