Я хотел бы вам предложить следующие строковые функции Transact-SQL
AT() Выполняет поиск позиции, с которой подстрока начинается в строке и возвращает номер позиции
RAT() Выполняет поиск позиции, с которой подстрока начинается в строке и возвращает номер позиции, но в отличии от AT() осуществляет поиск справа
OCCURS() Возвращает количество вхождений подстроки в строке (учитывает "перекрытия" подстрок)
OCCURS2() Возвращает количество вхождений подстроки в строке (не учитывает "перекрытия" подстрок)
PADL() Возвращает в виде символьной строки, заданную первым аргументом строку, дополненную третьим аргументом до длины, заданной вторым аргументом, причем вставляет символы-заполнители слева
PADR () Возвращает в виде символьной строки, заданную первым аргументом строку, дополненную третьим аргументом до длины, заданной вторым аргументом, причем вставляет символы-заполнители справа
PADC() Возвращает в виде символьной строки, заданную первым аргументом строку, дополненную третьим аргументом до длины, заданной вторым аргументом, причем вставляет символы-заполнители с обеих сторон
CHRTRAN() Выполняет перекодировку выражения, заданного в первой строке, используя вторую и третью строки в качестве таблицы перекодировки
STRTRAN() Возвращает строку символов, полученную из первой строки, в которой одно или несколько вхождений второй подстроки заменены третьей подстрокой(в отличии от встроенной функции replace, STRTRAN имеет три дополнительных параметра)
STRFILTER() Удаляет все символы из первой строки, за исключением символов, содержащихся во второй строке
GETWORDCOUNT() Возвращает количество слов в строке
GETWORDNUM() Возвращает заданное слово в строке
GETALLWORDS() Помещает в таблицу все слова из строки
PROPER() Возвращает строку, преобразуя в ней первую букву каждого слова в прописную, а остальные в строчные
RCHARINDEX () Подобна встроенной функции Transact-SQL charindex, но осуществляет поиск справа
ARABTOROMAN () Преобразует обычное число в римское число (от 1 до 3999)
ROMANTOARAB () Преобразует римское число в обычное число (от I до MMMCMXCIX)
Функции AT, PADL, PADR, CHRTRAN, PROPER аналогичны функциям Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP
Более 8000 человек во всем мире уже загрузили данные функции, надеюсь, что они будут полезными и для Вас.
Есть версии для Sybase ASA, PL DB2, Oracle PL/SQL
С целью получения более полной информации относительно вышеописанных функций (на английском языке), пожалуйста, зайдите на
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Пожалуйста, загрузите файл
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
Имеются версии для Sybase ASA и для DB2. Функции GETWORDCOUNT, GETWORDNUM, AT, RAT, OCCURS, CHRTRAN, STRTRAN, PADC, PADR, PADL, PROPER были и есть в VFP, функция STRFILTER есть в библиотеке Foxtools VFP.
Имена этих функций, вызов функций (за исключением того, что в Transact-SQL Вы не можете опускать параметры по умолчанию, нужно обязательно указывать ключевое слово default при вызове UDF), результаты работы - совпадают с одноименными функциями VFP.
Прилагаются тестовые программы для получения представления о скорости работы вышеупомянутых функций и сравнения их скорости работы со скоростью встроенных функций.
Имеются следующие особенности:
1) в VFP функция OCCURS подсчитывает количество вхождений подстрок в строку, не учитывая перекрытия (overlaps), то есть поиск n+1 подстроки в строке начинается
с элемента номер (начало n подстроки + длина подстроки).
Пример: в VFP OCCURS('ABCA', 'ABCABCABCA') -- display 2
-- 1 occurrence of substring 'ABCA .. BCABCA'
-- 2 occurrence of substring 'ABCABC ..ABCA'
Моя функция OCCURS подсчитывает количество вхождений подстрок в строку, учитывая перекрытия (overlaps), то есть
поиск n+1 подстроки в строке начинается
с элемента номер (начало n подстроки + 1).
Пример: select dbo.OCCURS('ABCA', 'ABCABCABCA') -- display 3
-- 1 occurrence of substring 'ABCA .. BCABCA'
-- 2 occurrence of substring 'ABC...ABCA...BCA'
-- 3 occurrence of substring 'ABCABC...ABCA'
То есть в VFP имеется некоторое противоречие -
At(cSearchExpression, cExpressionSearched, Occurs(cSearchExpression, cExpressionSearched) + 1) должно быть всегда равно нулю, но в VFP это не всегда так :
Выполните, пожалуйста, следующую программу, которая показывает противоречие в работе функций VFP At и Occurs
________________________________________________________________
cSearchExpression = 'ABCA'
cExpressionSearched = 'ABCABCABCA'
?' количество вхождений подстроки ' +cSearchExpression +' в строку '+cExpressionSearched+' , полученное с помощью функции Occurs'
lnIOccurs = Occurs(cSearchExpression , cExpressionSearched ) && display 2
? lnIOccurs
** первая подстрока
lnSrt = At(cSearchExpression, cExpressionSearched, 1)
lnI = 1
?' показывает все подстроки, полученные с помощью функции At'
Do While .Not.Empty(lnSrt)
?' подстрока номер ', lnI, ' начинающаяся с позиции номер ', lnSrt
?Substr( cExpressionSearched, lnSrt, Len(cSearchExpression))
?' оставшаяся строка '
?Space(lnSrt)+Substr( cExpressionSearched, lnSrt)
lnI = lnI + 1
lnSrt = At(cSearchExpression, cExpressionSearched, lnI )
Enddo
lnI = lnI - 1
?'количество подстрок, полученное с помощью функции At', lnI
?' количество подстрок найденных с помощью функции At больше '
?'чем количество подстрок полученных с помощью функции Occurs'
? lnI, Iif(lnI > lnIOccurs , ' > ', Iif(lnI < lnIOccurs, ' < ',' = ')) , lnIOccurs
?' следующее выражение '
?'At(cSearchExpression, cExpressionSearched, Occurs(cSearchExpression, cExpressionSearched) + 1)'
?'непустое - противоречие'
?' функция Occurs возвращает количество вхождение подстроки в строку'
?' функция At возвращает позицию, с которой начинается подстрока в строке, '
?'причем третий параметр это номер вхождения подстроки в строку'
?At(cSearchExpression, cExpressionSearched, Occurs(cSearchExpression, cExpressionSearched) + 1)
________________________________________________________________
Предположим, некто пишет цикл
For lnI = 1 to Occurs( Var1, Var2)
?at( Var1, Var2, lnI)
Endfor
Он уверен, что Occurs( Var1, Var2) всегда выдает результат, соотносящийся с результатами функции at,
на некоторых строках найти ошибку будет весьма трудно.
Сейчас вопрос касательно работы функции OCCURS задан VFP Team, но ответа пока нет.
Для подсчета количества вхождений подстрок в строку, не учитывая перекрытия (overlaps), Stephen Dobson (Toronto), предложил элегантный прием (с моими дополнениями), это выглядит так:
CREATE function OCCURS2 (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@cSearchExpression) > 0
then ( datalength(@cExpressionSearched)
- datalength(replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@cSearchExpression as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@cSearchExpression)
else 0
end
end
2) функции VFP GETWORDCOUNT, GETWORDNUM некорректно работают со строками, содержащими нулевой символ
3) особенность работы функции substring Transact-SQL.
если substr('tra-la-la', 0, 2) VFP возвращает пустую строку, то есть при втором аргументе меньше единицы всегда возвращается пустая строка, то
substring('tra-la-la', 0, 2) Transact-SQL возвращает 't' то есть при втором аргументе меньше единицы нужно смотреть на третий аргумент, и в зависимости от него будет получен результат, скажем,
substring('tra-la-la', -5, 8) Transact-SQL возвращает 'tr' .
Объяснение этому здесь http://support.microsoft.com/kb/q310421/
4) баг в Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) SP4
declare @n nvarchar(4000)
set @n = replicate(N'z',3999)+N'ï'
select right(replace(@n, N'ï', N'B'),1) -- display ï but this is incorrect result, correct result is B
Каждыйв этом может убедиться
Теперь вопросы, скептические замечания и мои ответы на них:
»Transact-SQL тоже язык не бедный и к чему изобретать велосипед в каждом языке родное надежнее
Лично мне напоминает мне то время когда я не знал FoxPro и изобретал разные функции, не зная о существовании встроенных, а теперь я напрочь о своих разработках .
На мой взгляд, лучше просто изучить подробнее стандартные функции Transact-SQL, там должно быть всё, ну, или почти все»
» Лично я стараюсь не использовать пользовательские функции в MS SQL Server... В принципе, пока обходился стандартными... А там уже не за горами версия от 2005 года...»
В начальных версиях FoxPro, затем VFP, не было функций GETWORDCOUNT, GETWORDNUM они были в библиотеке Foxtools, без них можно было обойтись, используя стандартные. Не было, кстати, функции ICASE она появилась, начиная с 9 версии. Начиная с шестой версии, многие функции из библиотеки Foxtools стали частью языка.
Если бы существовала острая необходимость в написанных мною функциях Transact-SQL, их написал бы не я, а разработчики SQL Server.
Острой необходимости в них нет, каждый день происходит 40-50 закачек, хотя объявления представлены в разделах скриптов на достаточно популярных форумах, например на
http://www.sqlservercentral.com/scripts/contributions/1488.asp
http://www.databasejournal.com/scripts/article.php/3503581
http://www.sswug.org/scriptmgr/recentscripts.asp .
и так далее
В мире сотни тысячи пользователей SQL Server, закачали же мои функции за полтора месяца со всех источников, наверное, чуть более 2500.
Я встретил поначалу массу скептических отзывов со стороны многих MVP, встретил, правда и положительные отзывы со стороны тех же MVP, в общем, мнения разделились.
Ну а тот, кому надо, берет и использует, просто многие из тех, кому они действительно могут быть полезными, не знают и не подозревают об их существовании.
» Ну не верю я в то, что сервер беднее, чем FoxPro»
Это не вопрос веры, а всего лишь знания, функций
GETWORDCOUNT, GETWORDNUM, AT, RAT, CHRTRAN, STRFILTER, OCCURS, PADC, PADR, PADL, PROPER (схожих не по имени, а по функциональности) в Transact-SQL нет, насчет версии 2005 не знаю. Найти какие либо еще отсутствующие функции мне не удалось (если только не считать совсем узкоспециальное, типа JUSTNAME и т.д.).
“родное надежнее”
Смотрите выше «баг в Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) SP4»
Именно по причине большого количества дешевых поделок, умные люди не используют «функции», «библиотеки» в большом количестве встречающиеся в Интернете. Начинаешь что-нибудь ставить,
а оно не устанавливается, как можно этому доверять. Как можно доверять серьезные приложения функциям написанным неизвестно кем? Поэтому со скептицизмом встречают и вышеприведенные функции, но они потихоньку находят своих пользователей, после публикации на серьезных форумах число скептиков уменьшается, по существу возразить никто не может.
Идея тестирования вышеприведенных функций очень проста - формируются случайные строки случайной длины, и затем сравниваются результаты работы встроенных функций VFP и UDF Transact-SQL. Я прогнал тесты несколько сотен тысяч раз, перед тем как попытался разместить их в разделе Downloads форума Universalthread.
Код открыт и всегда можно убедится в его корректности.
Так что все работает в соответствии со спецификацией VFP, за исключение того, что мои функции работают и со строками в UNICODE, то есть к примеру с символами китайского языка,
я размещал их и на китайском форуме SQL Server.
»Может пример приведете из своего опыта, когда что-то сделать невозможно при работе со строками»
Два примера, скорее обратных, показывающих, что со строками можно сделать все, используя встроенные функции, но не из моего опыта, поскольку, если задача встречается часто или может в будущем встретится часто, я предпочитаю писать процедуру или функцию.
Первый пример:
На 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 & SQLTeam MVY и прочая и прочая (всех титулов и званий достаточно много)
начинает давать дельные советы, что то про bcp и т.д. и т.п., разворачивается оживленная дискуссия
его благодарят и т.д. и т.п.
В общем, кому интересно, можете почитать.
»Ну а особенности с ними нужно дружить, а не воевать»
Их нужно сначала знать, для этого и существуют форумы, где обмениваются информацией,
Форумы ведь существуют не только для того чтобы отвечать на вопросы Dave ов. У меня ушло достаточно много времени на то чтобы разобраться, этого не знали даже (теперь знают от меня) гуру от VFP.
Ну и в заключение, привожу фрагмент письма, показывающий, то, что делает нашу работу приятной.
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
Благодарю Вас за то, что Вы потратили свое время, прочитав мою статью, надеюсь,
что данное чтение хоть немного будет полезным для Вас.
С наилучшими пожеланиями, Никифоров Игорь.
Автор публикации
Вложенные файлы
# |
Тип файла |
Размер |
Название |
|---|---|---|---|
| 1 | .zip | 295,56 КБ | 483udfs_transact-sql |