Строковые функции ms sql: Строковые функции (Transact-SQL) — SQL Server

Встроенные функции Transact-SQL — Клёвый код

Transact-SQL обладает весьма обширным набором функций…

Агрегатные функции:

AGV	Возвращает среднее арифметическое группы значний (столбца), при этом значение NULL пропускаются.
CHECKSUM_AGG	Возвращает контрольную сумму значений в группе. Значения NULL пропускаются. Можно использовать с OVER.
MAX	Возвращает максимальное значение в столбце.Можно использовать с OVER.
MIN	Возвращает минимальное значение в столбце.Можно использовать с OVER.
SUM	Сумма всех значений в столбце
COUNT	Возвращает количество значений столбца. Тип значения INT.Можно использовать с OVER.
COUNT_BIG	Возвращает количество значений столбца. Тип значения BIGINT. Можно использовать с OVER.
GROUPING	Возвращает столбец, который содержит значение 1, если строка добавлена с помощью операторов ROLLUP, CUBE и GROUPING SETS, или значение 0 в ином случае. Используется вместе с GROUP BY и HAVING.
GROUPING_ID	Возвращает уровень группирования.  Используется вместе с GROUP BY и HAVING. Появилась с 2008 версии.
STDEV	Возвращает статистическое стандартное отклонение всех значений в указанном выражении. Можно использовать с OVER.
STDEVP	Возвращает статистическое стандартное отклонение совокупности всех значений в указанном выражении. Можно использовать с OVER.
VAR	Возвращает статистическую дисперсию всех значений в указанном выражении. Можно использовать с OVER.
VARP	Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении. Можно использовать с OVER.

Скалярные функции:

Числовые функции:

SIN (f)	Возвращает синус f.
COS(f)	Возвращает косинус f.
COT (f)	Возвращает котангес f. y$$.
SQRT(f)	Возвращает квадратный корень f.
CEILING(n)	Возращает наименьшее целое число, большее или равное заданному параметру.
FLOOR (n)	Возвращает наибольшее целое число, которое меньше или равно заданному значению n.
ABS(n)	Возвращает модуль числа n.
SIGN (n)	Возвращает знак значения n в ввиде числа -1,0 или 1
#171;ROUND (n, l, [f])#187;	Возвращает округлённое значение числа n с точностью l. Если параметр f равен 1, то значение усекается а не округляется.
RAND	Возвращает случайное число в диапазоне от 0 до 1.
ROWCOUNT_BIG	Возвращает количество строк, которые были обработаны в процессе выполнения последнего оператора Transact-SQL.

Функции даты

SYSDATETIME()	Возвращает текущую системную дату и время. Тип datetime2(7). Появилась с 2008 версии.
SYSDATETIMEOFFSET()	Возвращает текущую системную дату и время, включая смещение часовго пояса .Тип  datetimeoffset(7). Появилась с 2008 версии.
SYSUTCDATETIME()	Возвращает текущую системную дату и время в формате UTC. Появилась с 2008 версии. Тип  datetime.
CURRENT_TIMESTAMP	Возвращает текущую системную дату и время. Тип  datetime.
GETDATE()	Возвращает текущую системную дату и время. Тип  datetime.
GETUTCDATE()	Возвращает текущую системную дату и время в формате UTC. Тип  datetime.
DATEPART (datepart, date)	Возвращает целое число являющееся частью даты date, заданную параметром datepart.
DATENAME(datepart, date)	Возвращает часть даты в виде строки символов. Дата указывается параметром date, а какю часть выводить задаётся параметром datepart.
DAY(date)	Возвращает день указанной даты.
MONTH(date)	Возвращает месяц указанной даты.
YEAR(date)	Возвращает год указанной даты.
DATEDIFF(datepart, date1, date2)	Возвращает целое число, являющуюся разностью между частями двух дат date1 и date2. В единицах заданных значением datepart.
DATEADD(datepart, number, date)	Возвращает дату являющуюся сложением даты date и интервалом.Интервал задаётся в единицах datepart и числом этих единиц number .
EOMONTH(date, add_month)	Возвращает последний день месяца, в текущем месяце указанной даты date. Можно выбрать другой месяц используя параметр add_month.
SWITCHOFFSET(datetimeoffset, time_zone)	Возвращает дату/время datetimeoffset, в часовым поясе time_zone. Достпуно с 2008 версии.
TODATETIMEOFFSET (datetime2, time_zone )	Возвращает значение даты в типе datetimeoffset. Преобразованием происходит путём добавления к дате datetime2 часового пояса time_zone.
ISDATE(expression)	Возвращает 1, если значение expression является значением datetime2.

Строковые функции

ASCII(char)	Возвращает ASCII-код первого символа.
CHAR(code)	Возвращает символ из ASCII-кода.
UNICODE(char)	Возвращает целое значение, для первого символа.
NCHAR(code)	Возвращает символ Unicode, заданный целочисленным кодом.
QUOTENAME(string)	Возвращает преобразованную строку Unicode с разделителями,  строка будет являтся допустим идентификатором с разделителями.
SOUNDEX(string)	Возвращает четырёхсимвольный код SOUNDEX, используемый для определения похожести звучания двух строк.
DIFFERENCE(string1, string2)	Возвращает разность между значениями SOUNDEX этих строк.
CHARINDEX(sub_string, string)	Возвращает начальную позицию подстроки в строке, если вхождений нет возвращает 0.
REPLACE(string, sub_string1, sub_string2)	Возвращает строку, где все подстроки sub_string1 в строке string заменены на подстроки sub_string2.
SUBSTRING(string, pos, length)	Возвращает подстроку строки string, начиная с позиции pos, длиной length.
LEFT(string, length)	Возвращает подстроку содержащую указанное число смволов с начала строки.
RIGHT(string, length)	Возвращает подстроку содержащую указанное число символов с конца строки.
LEN(string)	Возвращает кол-во символов в заданном строковом выражении, исключая конечные пробелы.
LOWER(string)	Возращает строку, где все символы верхнего регистра данной строки преобразованны в нижний регистр.
UPPER(string)	Возращает строку, где все символы нижнего регистра данной строки преобразованны в верхний регистр.
LTRIM(string)	Возвращает данную строку, без пробелов в начале.
RTRIM(string)	Возвращает данную строку, без пробелов в конце.
SPACE(length)	Возвращает пробельную строку с длиной, заданной параметром lenght.
REPLICATE(string, num)	Возвращает строку, где строка string повторяется num раз.
PATINDEX(%p%, expr)	Возвращает начальную позицию первого вхождения шаблона p в заданном выражении expr или ноль, если шаблон в строке не найден.
REVERSE(z)	Отображает строку z в реверсном, обратном порядке.
STR(f[, len[, d]])	Преобразует заданное выражение с плавающей точкой f в строку. Параметр len — длина строки, включая десятичную точку, знак числа, цифры и пробелы(По умолчанию 10), d — количество цифр справа от десятичной точки.
STUFF (z1, a, length, z2)	Заменяет часть строки z1 на строку z2, начиная с позиции a, заменяя length символов строки z1

Функции преобразования

CAST(expression AS type [(length)])	Преобразует выражение expression в заданный тип данных type (если это возможно). Параметр a может быть любым допустимым выражением.
CONVERT(type[(length)],expression [, style])	Эквивалетно CAST, но аргументы задаются иным образом. Функция CONVERT может быть использована с любым типом данных.
PARSE ( string_value AS data_type [ USING culture ] )	Возвращает значение string_value, приведённое к типу data_type. В случае ошибки выводит сообщение. Появилась с 2012 версии.
TRY_CAST ( expression AS data_type [ ( length ) ] )	Возвращает значение expression, приведённое к типу data_type. В случае не возможности это сделать возвращает NULL. Появилась с 2012 версии.
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )	Возвращает значение expression, приведённое к типу data_type. В случае не возможности это сделать возвращает NULL. Появилась с 2012 версии.
TRY_PARSE ( string_value AS data_type [ USING culture ] )	Возвращает значение expression, приведённое к типу data_type. В случае не возможности это сделать возвращает NULL. Рекомендуется использовать TRY_PARSE только для преобразования из строкового типа, в тип даты и времени или в числовой тип. Появилась с 2012 версии.

Системные функции

[ database_name. ] $PARTITION.partition_function_name(expression)	Возвращает номер секции для любого допустимого значения.
@@IDENTITY	Возвращает значение последнего вставленого идентификатора.
@@PACK_RECEIVED	Возвращает количество входных пакетов с последнего запуска.
@@TRANCOUNT	Возвращает число инструкций BEGIN TRANSACTION, выполненных в текущем соединении.
@@ROWCOUNT	Возвращает число строк затронутых при выполнении последней инструкции. Возвращаемый тип int.
ROWCOUNT_BIG()	Возвращает число строк, затронутых при выполнении последней инструкции. Тип	except the return type of ROWCOUNT_BIG is <span><span>bigint</span></span>. «>bigint.
@@ERROR	Возвращает код ошибки последней выполненной инструкции. Если ошибок небыло выводит 0.
ERROR_LINE()	Возвращает номер строки, в которой возникла ошибка. Которая возникла в конструкции TRY…CATCH.
ERROR_MESSAGE()	Возвращает текст сообщения об ошибке, которая возникла в конструкции TRY…CATCH.
ERROR_NUMBER()	Возвращает номер ошибки, которая возникла в конструкции TRY…CATCH.
ERROR_PROCEDURE()	Возвращает имя хранимой процедуры или триггера, в которых произошла ошибка. Возникшая в конструкции TRY…CATCH.
ERROR_SEVERITY()	Возвращает серьезность ошибки, которая возникла в конструкции TRY…CATCH.
ERROR_STATE()	Возвращает номер состояния ошибки, которая возникла в конструкции TRY…CATCH.
BINARY_CHECKSUM ( * | expression [ ,…n ] )	Возвращает двоичное значение контрольной суммы, вычисленное для строки таблицы или для списка выражений.
CHECKSUM ( * | expression [ ,…n ] )	Возвращает значение контрольной суммы, вычисленное для строки таблицы или для списка выражений.
FORMATMESSAGE ( msg_number, [ param_value [ ,…n ] ] )	Возвращает сформированный текст полученный из сообщений имеющиеся в sys.messages.
GET_FILESTREAM_TRANSACTION_CONTEXT ()	Возвращает токен, который представляет текущий контекст транзакции сеанса.
CONNECTIONPROPERTY ( property )	Возвращает свойство property соединения, по которому получен запрос.
CURRENT_REQUEST_ID()	Возвращает идентификатор текущего требования в пределах текущего сеанса.
HOST_ID ()	Возвращает идентификационный номер рабочей станции.
HOST_NAME ()	Возвращает имя рабочей станции.
GETANSINULL ( [ ‘database’ ] )	Возвращает 1, если использование значений NULL в базе данных dbname соответствует станадарту ANSI SQL.
ISNULL ( check_expression, replacement_value )	Возвращает значение выражения check_expression, если оно не является NULL. Иначе возвращает значение replacement_value.
ISNUMERIC(expression)	Возвращает 1 если выражение expression является допуститмым числовым выражением, иначе 0.
MIN_ACTIVE_ROWVERSION	Возвращает наименьшее активное значение rowversion в текущей базе данных.
NEWID()	Возвращает уникальный номер ID, который состоит из 16-байтовой двоичной строки. Тип данных uniqueidentifier.
NEWSEQUENTIALID()	Возвращает GUID, который больше любого сгенерированного ранее этой функцией на данном компьютере GUID.(Эта функция может быть использованна только в качестве значения по умолчанию для столбца.)
XACT_STATE()	Возвращает 1 если текущий запрос содержит активную пользовательскую транзакцию. 0 — у текущего запроса нет активной пользовательской транзакции. -1 — в	but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.»> текущем запросе есть активная транзакция, однако произошла ошибка, из-за которой транзакция классифицируется как нефиксируемая.

Функции безопастности:

CERTENCODED ( cert_id )	Возвращает открытую часть сертификата в двоичном формате.
CERTPRIVATEKEY (cert_ID, ‘encryption_password ‘[,’decryption_password ‘])	Возвращает закрытый ключ сертификата в двоичном формате.
DATABASE_PRINCIPAL_ID ( ‘principal_name’ )	Возвращает идентицикатор принципиала, в текущей базе.
sys.fn_builtin_permissions (  ‘<securable_class>’  )	Возвращает описание иерархии встроенных разрешений сервера.
fn_get_audit_file ( file_pattern, initial_file_name , audit_record_offset)	Возвращает сведения из файла аудита.
fn_my_permissions ( securable , ‘securable_class’ )	Возвращает список разрешений на защищаемый объект, фактически предоставленных участнику.
HAS_PERMS_BY_NAME ( securable, securable_class, permission[, sub-securable ] [, sub-securable_class ] )	Возвращает результат проверки доступа у текущего участника на защищаемый объект. Если права имеются возвращается 1, иначе 0.
IS_MEMBER ( { ‘group’ | ‘role’ } )	Возвращает результат проверки, является ли текущий участник членом указанной группы или роли.
IS_ROLEMEMBER ( ‘role’ [ , ‘database_principal’ ] )	Возвращает результат проверки, является ли текущий участник членом роли в указанной базе данных.
IS_SRVROLEMEMBER ( ‘role’ [ , ‘login’ ] )	Возвращает результат проверки, является ли данная учётная запись login членом роли role.
ORIGINAL_LOGIN()	Возвращает текущее имя входа.
PERMISSIONS ( [ objectid [ , ‘column’ ] ] )		object	or column permissions of the current user. «>Возвращает битовую карту, указывающую разрешения на инструкции, объекты и столбцы для текущего пользователя.
PWDCOMPARE ( ‘clear_text_password’, password_hash [ , version ] )	Хэширует пароль и сравнивает хэш с хэшем существующего пароля.
PWDENCRYPT ( ‘password’ )(устаревшее) HASHBYTES ( ‘<algorithm>’, { @input | ‘input’ } )		MD4	MD5	SHA	SHA1	or SHA2 hash of its input in SQL Server.»>Возвращает хэш входного значения.
SCHEMA_ID ( [ schema_name ] )	Возвращает идентификатор схемы, связанный с именем схемы.
SCHEMA_NAME ( [ schema_id ] )	Возвращает имя схемы, связанное с идентификатором схемы.
SESSION_USER	Возвращает имя пользователя текущего контекста в текущей базе данных.
SUSER_ID ( [ ‘login’ ] )	Возвращает идентификационный номер имени входа пользователя.
SUSER_SID ( [ ‘login’ ] [ , Param2 ] )	Возвращает идентификатор безопасности (SID) для указанного имени входа.
SUSER_SNAME ( [ server_user_sid ] )	Возвращает имя входа, связанное с идентификатором безопасности (SID).
SYSTEM_USER	Возвращает ID учётной записи текущего пользователя.
SUSER_NAME ( [ server_user_id ] )	Возвращает идентификационное имя учетной записи пользователя.
USER_ID(user_name)	Возвращает идентификатор пользователя user_name. Если имя пользователя не задано, то возвращается идентификатор текущего пользователя.
USER_NAME([id])	Возвращает имя пользователя с идентификатором id. Если идентификатор не указан, возвращается имя текущего пользователя.
CURRENT_USER	Возвращает имя текущего пользователя.

Функции типов данных:

DATALENGTH ( expression )	Вычисляет длину(в байтах) результата, полученного из выражения expression.
IDENT_CURRENT( ‘table_name’ )	Возврат последнего значения идентификатора, созданного для указанной таблицы или представления.
IDENT_INCR ( ‘table_or_view’ )		0)) specified during the creation of an identity column in a table or view that has an identity column. «>Возвращает добавочное значение (тип numeric (@@MAXPRECISION,0)), указанное во время создания столбца с уникальным идентификатором в таблице или представлении, которые содержат столбец идентификаторов.
IDENT_SEED ( ‘table_or_view’ )		0)) that was specified when an identity column in a table or a view was created.»>Возвращает исходное начальное значение (вида numeric(@@MAXPRECISION,0)), указанное для столбца идентификаторов при создании таблицы или представления.
IDENTITY (data_type [ , seed , increment ] ) AS column_name	Используется только в инструкции SELECT с предложением INTO table для вставки столбца идентификаторов в новую таблицу. Хотя они похожи, функция IDENTITY не является свойством IDENTITY, которое используется с инструкциями CREATE TABLE и ALTER TABLE.
SQL_VARIANT_PROPERTY ( expression, property )	Возвращает базовый тип данных и другие сведения о значении sql_variant.

Функции метаданных

APPLOCK_MODE( ‘database_principal’ , ‘resource_name’ , ‘lock_owner’ )	Возвращает режим блокировки ресурса resource_name, с владельцем блокировки	which is the <span>lock_owner</span> value when the lock was requested.»>lock_owner, и правами пользоватля или ролью database_principal.
APPLOCK_TEST ( ‘database_principal’ , ‘resource_name’ , ‘lock_mode’ , ‘lock_owner’ )	Возвращает 0 когда блокировка не может быть предоставлена указанному владельцу, иначе 1.
@@PROCID	Возвращает идентификатор текущегей хранимой процедуры, пользовательской функции или триггера. Не вызывается из CLR среды или внутрипроцессного поставщика доступа к данным.
APP_NAME()	Возвращает имя приложения, для текущего сеанса.
ASSEMBLYPROPERTY(‘assembly_name’, ‘property_name’)	Возвращает свойство property_name сборки assembly_name.
COL_LENGTH ( ‘table’ , ‘column’ )	Возвращает длину в байтах столбца column в таблице table.
COL_NAME(tab_id, col_id)	Возвращает имя столбца с идентификатором col_id из таблицы, имеющей идентификатор tab_id.
INDEX_COL ( ‘[ database_name . [ schema_name ] .| schema_name ] table_or_view_name’, index_id , key_id )	Возвращает имя индексированного столбца в таблице table_or_view_name. Столбец определяется по идентификатору индекса index_id и позиции столбца  key_id в этом индексе.
COLUMNPROPERTY (id, col, property)	Возвращает свойство property стобца col, в таблице или процедуры заданной идентификатором id.
DATABASE_PRINCIPAL_ID ( ‘principal_name’ )	Возвращает идентификатор принципала по его имени name, если не указать имя выводиться идентификатор текущего принципала.
DATABASEPROPERTYEX (database, property)	Возвращает значение свойств property, из базы данных  database.
DB_ID ([‘db_name’])	Возвращает идентификатор базы данных с именем db_name. Если имя не задано, возвращает идентификатор текущей базы данных.
DB_NAME ([db_id])	Возвращает имя базы данных с идентификатором b_id. Если идентификатор не задан, возвращает имя текущей базы данных.
FILE_ID ( file_name )	Возвращает идентификатор файла в текущей базе данных с имем file_name.
FILE_IDEX ( file_name )	Возвращает идентификатор	log	or full-text file in the current database.»> файла данных, файла журнала, или полнотекстового файла в текущей базе данных с имем file_name.
FILE_NAME ( file_id )	Возвращает имя файла в текущей базе данных с идентификатором file_id.
FILEPROPERTY ( file_name , property )	Возвращает свойство property файла file_name.
FILEGROUP_ID ( ‘filegroup_name’ )	Возвращает по имени файловой группы filegroup_name, её идентификатор.
FILEGROUP_NAME (filegroup_id)	Возвращает имя файловой группы, по её идентификатору filegroup_id.
FILEGROUPPROPERTY ( filegroup_name, property )	Возвращает свойство property файловой группы filegroup_name.
FULLTEXTCATALOGPROPERTY (‘catalog_name’, ‘property’)	Возвращает свойство property полнотекстового каталога catalog_name. Появилась с 2014 версии.
FULLTEXTSERVICEPROPERTY (‘property’)	Возвращает свойство property механизма полнотекстового поиска.
INDEXKEY_PROPERTY ( object_ID, index_ID, key_ID, property )	Возвращает свойства property ключей индекса. Для XML-индексов возвращает NULL.
INDEXPROPERTY ( object_ID, index_or_statistics_name, property )	Возвращает свойство property именованного индекса или статистическое свойство таблицы. Заданной идентификационным номером, именем индекса, или статистики, или имененм свойства. Для XML-индексов возвращает NULL.
NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name  [ OVER (<over_order_by_clause>) ]	Формирует номер последовательности из указанного объекта последовательности.
OBJECT_DEFINITION ( object_id )	Возвращает исходный код, указанного объекта. Если не хватает прав или произошла ошибка, возвращает NULL.
OBJECT_ID ( ‘[ database_name . [ schema_name ] . | schema_name . ] object_name’ [,’object_type’ ] )	Возвращает идентификатор объекта базы данных, заданного именем object_name.
SCOPE_IDENTITY()	Возвращает последнее значение идентификатора, созданные в таблицах во время текущего сеанса.
OBJECT_NAME ( object_id [, database_id ] )	Возвращает имя объекта базы данных, заданного своим идентификатором object_id.
OBJECT_SCHEMA_NAME ( object_id [, database_id ] )	Возвращает имя схемы базы данных.
OBJECTPROPERTY ( id, property )	Возвращает информацию об объектах текущей базы данных.
OBJECTPROPERTYEX ( id, property )	Возвращает информацию об объектах текущей базы данных.
ORIGINAL_DB_NAME ()	Возвращает имя базы данных, указанное при подключении к базе данных.
PARSENAME ( ‘object_name’, object_piece )	Возвращает часть имени базы данных object_name. Часть объекта выберается параметром object_piece.
SCHEMA_ID ( [ schema_name ] )	Возвращает идентификатор схемы schema_name. Если имя схемы является недопустимым, возвращает NULL.
SCHEMA_NAME ( [ schema_id ] )	Возвращает имя схемы, по идентификатору schema_id. Если идентификатор является недопустимым, возвращает NULL.
SERVERPROPERTY ( propertyname )	Возвращает свойство  propertyname экземпляра сервера.
STATS_DATE ( object_id, stats_id )	Возвращает дату последнего обновления статистики для таблицы или индексированного представления.
TYPE_ID ( [ schema_name ] type_name )	Возвращает идентификатор типа данных type_name.
TYPE_NAME ( type_id )	Возвращает короткое имя типа, по идентификатору type_id.
TYPEPROPERTY (type, property)	Возвращает свойство property, типа данных type.

Выражения:

CASE input_expression        WHEN when_expression THEN result_expression      …      [ ELSE else_result_expression ] END	Возвращает result_expression если  input_expression=when_expression.
CASE        WHEN Boolean_expression THEN result_expression      …      [ ELSE else_result_expression ] END	Возвращает result_expression если  Boolean_expression=TRUE.
NULLIF(expr1, expr2)	Возвращает NULL, если значения выражений exp1 и exp2 равны.
COALESCE(a1, a2, …)	Возвращает значение первого выражения, из списка (a1, a2, …), которое не является NULL.
CHOOSE ( index, val_1, val_2 [, val_n ] )	Возвращает элемент val_ с номером index.
IIF ( boolean_expression, true_value, false_value )	Возвращает значение true_value, если выражение boolean_expression=TRUE. Иначе возвращает false_value.

Функции работы с курсорами:

@@CURSOR_ROWS	Возвращает число выбранных строк, имеющихся в последнем открытом курсоре в данном соединении.	SQL Server can populate large keyset and static cursors asynchronously.»>Для повышения производительности SQL Server может выполнять заполнение большого набора ключей и статических курсоров асинхронно. Функция @@CURSOR_ROWS может быть вызвана для определения того, что количество строк, определенных для курсора, получено во время вызова @@CURSOR_ROWS.
@@FETCH_STATUS	Возвращает состояние последней инструкции FETCH, вызванной в любом курсоре, открытом в данном соединении.
CURSOR_STATUS({‘local’, ‘cursor_name’}|{‘global’, ‘cursor_name’ }|{‘variable’, ‘cursor_variable’ })	Скалярная функция, позволяющая при вызове хранимой процедуры определить, вернула ли она курсор и результирующий набор для данного параметра.

Описание и примеры строковых функций – Self-Learning – Авторские видеокурсы и книги по IT для начинающих

Текстовый материал

Многие алгоритмы, которые чаще всего требуются в повседневной работе, разработчики Microsoft SQL Server уже реализовали в виде системных функций.

Например, Вам уже известны агрегатные функции, которые мы затрагивали на предыдущем уровне курса. К ним относятся COUNT, SUM, MAX, MIN, AVG, с помощью них мы можем получить, например, сумму значений в столбце или количество записей.

Однако существуют и другие системные функции, которые позволяют выполнить не менее полезные операции, чем агрегация данных.

Все системные функции делятся на условные группы по своему назначению. В этом уроке Вы познакомитесь со строковыми функциями.

Строковые функции

Строковые функции – это функции, которые работают со строками, иными словами, с текстовыми значениями в столбце, т.е. выполняют различные операции над текстом.

Перед тем как начать рассматривать строковые функции, давайте создадим тестовые данные.

Для этого выполните SQL инструкции из скрипта, который приложен к этому курсу. Данные инструкции создают необходимые таблицы и наполняют их тестовыми данными. Эти таблицы нам будут требоваться на протяжении всего этого курса, т.е. именно с этими данными мы и будем работать.

В результате выполнения этих инструкций Вы получите вот такие данные.

LTRIM, RTRIM и TRIM

Нередко случается, что в текстовом столбце в таблице в начале или в конце строки, пользователи могут занести пробелы, а они также являются символами, и при обработке подобных значений могут возникнуть некоторые проблемы. Поэтому в SQL сервере есть специальные функции, которые в запросе могут возвращать данные без этих пробелов, т.е. убрать все пробелы в начале или в конце.

Функция LTRIM убирает все пробелы в начале строки, функция RTRIM в конце.

Во многие системные функции необходимо передавать параметры. В функции LTRIM и RTRIM нужно передать саму текстовую строку, из которой нужно удалить пробелы, это может быть как просто текст, так и столбец, т.е. название столбца в таблице.

Для примера давайте напишем запрос, в котором мы обратимся к таблице Goods, и получим все значения столбца ProductName без пробелов в начале и в конце строки, которые есть в этом столбце, такие данные мы специально туда добавили.

Для наглядности столбец ProductName мы выведем несколько раз, чтобы видеть, как изменяется результирующий набор данных. Также здесь показано, что функции можно сочетать, т.е. вкладывать друг в друга, например, в данном запросе мы к столбцу ProductName сначала применили функцию LTRIM, а затем RTRIM (колонка [RTRIM и LTRIM]), таким образом, мы сразу удалили пробелы как в начале, так и в конце. Однако в новых версиях SQL сервер появилась функция TRIM, которая делает то же самое, т.е. использование двух функций RTRIM и LTRIM для удаления всех пробелов как в начале, так и в конце, теперь не требуется.

SELECT ProductName AS [Исходное значение],
       LTRIM(ProductName) AS [LTRIM],
       RTRIM(ProductName) AS [RTRIM],
       RTRIM(LTRIM(ProductName)) AS [RTRIM и LTRIM],
       TRIM(ProductName) AS [TRIM]
FROM Goods;

LOWER и UPPER

Следующие функции, которые стоит рассмотреть – это LOWER и UPPER, так как бывают случаи, что нужно значение всей строки получить в определённом регистре, в верхнем или в нижнем. Эти функции в языке T-SQL используются именно для этих целей.

• UPPER – возвращает все символы указанной строки в верхнем регистре;
• LOWER – возвращает все символы указанной строки в нижнем регистре.

В данном примере наглядно видно, как работают данные функции.

SELECT ProductName AS [Исходное значение],
       LOWER (ProductName) AS [LOWER],
       UPPER(ProductName) AS [UPPER]
FROM Goods;

LEN

Для того чтобы определить количество символов в строке, можно использовать функцию LEN.

Например.

SELECT ProductName AS [Исходное значение],
       LEN(ProductName) AS [LEN],
       LEN(LTRIM(ProductName)) AS [LTRIM и LEN]
FROM Goods;

Мы видим, сколько символов содержит каждая строка таблицы.

При этом стоит отметить, что пробелы в начале строки считаются символами и их учитывает функция LEN.

LEFT и RIGHT

Если Вам нужно получить не всю строку, а только первые несколько символов или последние несколько символов, можно использовать функции LEFT и RIGHT.

LEFT возвращает символы слева, RIGHT соответственно справа.

Эти функции принимают уже два параметра, первый – это строка, а второй – количество символов, которые нужно получить.

SELECT ProductName AS [Исходное значение],
       LEFT(ProductName, 3) AS [LEFT],
       RIGHT(ProductName, 3) AS [RIGHT],
       LEFT(LTRIM(ProductName), 3) AS [LEFT и LTRIM],
       RIGHT(RTRIM(ProductName), 3) AS [RIGHT и RTRIM]
FROM Goods;

В данном запросе мы выводим по 3 символа. Также здесь показано, что пробелы в начале учитываются и этими функциями.

SUBSTRING

Далее, давайте рассмотрим функцию SUBSTRING, с помощью которой можно вывести определенную часть строки, например, начиная с указанной позиции, и определенное количество символов. В данную функцию необходимо передать три параметра, первый – строка, второй – начальная позиция, третий – количество символов.

В данном случае мы выводим часть строки, а именно всего 5 символов, начиная с 3 символа.

SELECT ProductName AS [Исходное значение],
       SUBSTRING(ProductName, 3, 5) AS [SUBSTRING]
FROM Goods;

Полный перечень строковых функций в Microsoft SQL Server

• LTRIM – возвращает строку после удаления начальных пробелов
• RTRIM – возвращает строку символов, из которой удалены все завершающие пробелы
• TRIM – удаляет символ пробела char(32) в начале и конце строки
• LOWER – возвращает строку, в которой символы верхнего регистра преобразованы в символы нижнего регистра
• UPPER – возвращает строку, в которой символы нижнего регистра преобразованы в символы верхнего регистра
• LEN – возвращает количество символов указанного строкового выражения, исключая конечные пробелы
• LEFT – возвращает указанное число символов символьного выражения слева
• RIGHT – возвращает указанное число символов символьной строки справа
• SUBSTRING – возвращает часть строки, начиная с указанной позиции, и определенное количество символов
• REPLACE – заменяет все вхождения указанного строкового значения другим строковым значением
• REPLICATE – повторяет значение строки указанное число раз
• REVERSE – возвращает строковое значение, где символы переставлены в обратном порядке справа налево
• CONCAT – функция возвращает строку, которая формируется в результате объединения двух или более строковых значений
• CONCAT_WS – функция возвращает строку, которая формируется в результате объединения двух или более строковых значений. Однако в отличие от CONCAT она разделяет значения в результирующей строке с помощью разделителя, указанного в первом аргументе функции
• ASCII – возвращает код ASCII первого символа указанного символьного выражения
• CHAR – функция преобразует код ASCII в значение символа
• CHARINDEX – функция выполняет поиск одной строки внутри другой строки, возвращая начальную позицию первого выражения, если оно найдено
• DIFFERENCE – функция возвращает целочисленную разницу между значениями SOUNDEX двух разных символьных выражений
• FORMAT – возвращает значение, отформатированное с использованием указанного формата, языка (необязательно) и региональных параметров
• NCHAR – возвращает символ Юникода с указанным целочисленным кодом, определенным в стандарте Юникода
• PATINDEX – для любого текстового типа данных возвращает начальную позицию первого вхождения шаблона в указанном выражении или нули, если шаблон не найден
• QUOTENAME – возвращает Юникод-строку с разделителями, образуя из строки ввода правильный идентификатор с разделителем SQL Server
• SOUNDEX – возвращает четырехсимвольный код (SOUNDEX) для оценки степени сходства двух строк
• SPACE – возвращает строку пробелов
• STR – возвращает символьные данные, преобразованные из числовых данных
• STRING_AGG – сцепляет значения строковых выражений, помещая между ними значения-разделители. В конце строки разделитель не добавляется
• STRING_ESCAPE – экранирует специальные символы в тексте и возвращает текст с экранированными символами. STRING_ESCAPE – это детерминированная функция
• STRING_SPLIT – функция с табличным значением, которая разбивает строку на строки подстрок в зависимости от указанного знака разделения
• STUFF – функция вставляет одну строку в другую. Она удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место вторую строку.
• TRANSLATE – возвращает строку, предоставленную в качестве первого параметра, после преобразования символов, указанных во втором параметре, в конечный набор символов, указанный в третьем параметре
• UNICODE – возвращает целочисленное значение, соответствующее стандарту Юникод, для первого символа входного выражения

Стоимость полного курса 1990 ₽

Оплатить курс

SQL Server Helper — строковые функции SQL Server

Главная  >  Функции  > Строковые функции SQL Server

Строковые функции SQL Server

Строковые функции SQL Server Строковые функции SQL Server — это скалярные функции, которые выполняют операцию над строковым входным значением и возвращают строковое или числовое значение. Все встроенные строковые функции, кроме CHARINDEX и PATINDEX, являются детерминированными. Это означает, что они возвращают одно и то же значение каждый раз, когда вызываются с определенным набором входных значений. Функция Описание ASCII-код Возвращает кодовое значение ASCII крайнего левого символа символьного выражения. Синтаксис: ASCII (символьное_выражение) СИМВОЛ Преобразует код int ASCII в символ. Синтаксис: СИМВОЛ ( целочисленное_выражение ) ЧАРИНДЕКС Возвращает начальную позицию указанного выражения в строке символов. Синтаксис: CHARINDEX (выражение1, выражение2 [ начальное_местоположение]) РАЗНИЦА Возвращает целочисленное значение, указывающее разницу между значениями SOUNDEX двух символьных выражений. Синтаксис: РАЗНИЦА (символьное_выражение, символьное_выражение) ЛЕВЫЙ Возвращает левую часть строки символов с указанным количеством символов. Синтаксис: ЛЕВЫЙ (символьное_выражение, целое_выражение) ЛЕН Возвращает количество символов вместо количества байтов указанного строкового выражения, исключая конечные пробелы. Синтаксис: ДЛСТР (строковое_выражение) НИЖНИЙ Возвращает символьное выражение после преобразования прописных символов в строчные. Синтаксис: НИЖЕ (строковое_выражение) LTRIM Возвращает символьное выражение после удаления начальных пробелов. Синтаксис: LTRIM (string_expression) НЧАР Возвращает символ Unicode с указанным целочисленным кодом, как определено стандартом Unicode. Синтаксис: NCHAR ( целочисленное_выражение ) ПАТИНДЕКС Возвращает начальную позицию первого появления шаблона в указанном выражении или нули, если шаблон не найден, для всех допустимых текстовых и символьных типов данных. Синтаксис: PATINDEX ('%pattern%', выражение) QUOTENAME Возвращает строку Unicode с добавленными разделителями, чтобы сделать входную строку допустимым идентификатором Microsoft SQL Server 2005 с разделителями. Синтаксис: QUOTENAME ( 'character_string' [ , 'quote_character' ] ) ЗАМЕНИТЬ Заменяет все вхождения второго указанного строкового выражения в первое строковое выражение третьим выражением. Синтаксис: ЗАМЕНИТЬ ('string_expression1', 'string_expression2', 'string_expression3') Функция Описание РЕПЛИКА Повторяет символьное выражение заданное количество раз. Синтаксис: РЕПЛИКАЦИЯ (символьное_выражение, целое_выражение) ЗАДНИЙ Возвращает обратную сторону символьного выражения. Синтаксис: ОБРАТНОЕ (символьное_выражение) СПРАВА Возвращает правую часть строки символов с указанным количеством символов. Синтаксис: ПРАВО (символьное_выражение, целочисленное_выражение) РТРИМ Возвращает строку символов после усечения всех завершающих пробелов. Синтаксис: RTRIM (символьное_выражение) САУНДЭКС Возвращает четырехсимвольный код (SOUNDEX) для оценки сходства двух строк. Синтаксис: SOUNDEX (символьное_выражение) ПРОБЕЛ Возвращает строку повторяющихся пробелов. Синтаксис: ПРОБЕЛ ( целочисленное_выражение ) СТР Возвращает символьные данные, преобразованные из числовых данных. Синтаксис: STR ( float_expression [ , длина [ , ] ] ) МАТЕРИАЛЫ Удаляет указанную длину символов и вставляет другой набор символов в указанной начальной точке. Синтаксис: STUFF (символьное_выражение, начало, длина, символьное_выражение) ПОДСТРОКА Возвращает часть символьного, двоичного, текстового или графического выражения. Синтаксис: ПОДСТРОКА (выражение, начало, длина) ЮНИКОД Возвращает целочисленное значение, определенное стандартом Unicode, для первого символа входного выражения. Синтаксис: UNICODE ('ncharacter_expression') ВЕРХНИЙ Возвращает символьное выражение с символьными данными нижнего регистра, преобразованными в верхний регистр. Синтаксис: ЗАГЛАВНАЯ (символьное_выражение)

Функция подсчета вхождения символов Функция подсчета строковых вхождений Функция подсчета слов

Строковые функции SQL — средний уровень SQL

Базы данных SQL предоставляют несколько типов данных SQL, связанных с текстовыми строками, например 9. 0303 VARCHAR , CHAR и ТЕКСТ . В этом уроке вы изучите некоторые строковые функции и операторы для управления строковыми данными.

Вот образец таблицы, используемый в примерах. На основе примера с яблоком в базу данных Apple добавляется новая таблица apple_sells . Таблица apple_sells хранит одну запись на каждую транзакцию продажи яблока.
Схема таблицы:

Имя клиента	Разнообразие яблок	Дата продажи	тонн продано	Цена тонны	Дата поставки	Замораживание при запуске
Смарт	Красный вкусный	23.02.2020	60	54,56	28.02.2020	08.01.2020 10:00
ДЖБ	Красный Глобус	2020-03-02	35	61,50	12.03.2020	15.01.2020 09:30
ДЖБ	Волшебный зеленый	13. 04.2020	15	58,50	22.04.2020	07.01.2020 08:23
Смарт	Красный Глобус	21.05.2020	100	61,00	28.05.2020	15.01.2020 09:30
Смарт	Красный вкусный	21.05.2020	90	54,56	30.05.2020	08.01.2020 10:00

Столбец startup_freeze отслеживает, когда яблоки попали в холодильные камеры. Эта информация предоставляется клиентам для соблюдения правил пищевых продуктов.

Оператор SQL LIKE

Одним из наиболее часто используемых операторов SQL, связанных со строками, является LIKE . Этот оператор позволяет создавать логические выражения: «Все имена, начинающиеся с «Je», или все города, содержащие «Lake» в середине, или фамилии, заканчивающиеся на «son». Оператор LIKE используется в сочетании с двумя подстановочными знаками: % (процент) и _ (подчеркивание)

Этот запрос возвращает записи о продажах всех разновидностей, начинающихся с Red :

ВЫБЕРИТЕ имя_клиента,
        яблоко_сорт,
        Дата доставки,
  ОТ apple_sells
 ГДЕ apple_variety НРАВИТСЯ «Красный%»
 

Вот результат:

имя_клиента	яблоко_сорт	delivery_date
Смарт	Красный вкусный	28. 02.2020
ДЖБ	Красный Глобус	12.03.2020
Смарт	Красный Глобус	28.05.2020
Смарт	Красный вкусный	2020-05-30

Часто используемой текстовой операцией является объединение строк. Эта операция зависит от диалекта SQL. Например, некоторые диалекты SQL реализуют его с помощью символа двойной вертикальной черты (||), а другие используют функцию CONCAT .

В этом примере создается отчет с одним столбцом с помощью || синтаксис:

ВЫБЕРИТЕ имя_клиента || «приказано» || тонн_продано ||
        'из' || яблоко_сорт || 'должен быть доставлен' ||
        delivery_date КАК order_description
  ОТ apple_sells
 

Результат:

Описание заказа
Компания SMart заказала 60 штук Red Delicious с доставкой 28 февраля 2020 г.
Компания JB заказала 35 самолетов Red Globus с доставкой 12 марта 2020 г.
JB заказал 15 штук Magic Green с доставкой 22 апреля 2020 г.
Компания SMart заказала 100 штук Red Globus с доставкой 28 мая 2020 г.
SMart заказал 90 штук Red Delicious с доставкой 30 мая 2020 г.

Функции SQL для работы с текстовыми строками

Существует несколько функций для работы с текстовыми строками, в том числе:

• Получение длины строки
• Преобразование строки в верхний или нижний регистр
• Извлечь подстроку из строки
• Удаление пустых символов с начала или конца строки

Частые ошибки базы данных — это лишние пробелы в конце строки или сочетание букв верхнего и нижнего регистра, что может вызвать проблемы с запросами и результатами. Когда вы сравниваете текстовый столбец с текстовой константой, вы можете преобразовать строку в нижний регистр и удалить все лишние пробелы перед выполнением сравнения:

ВЫБЕРИТЕ имя_клиента,
        яблоко_сорт,
        Дата доставки,
        CAST(дата_доставки КАК TIMESTAMP) - начало_заморозки КАК время_заморозки
  ОТ apple_sells
 ГДЕ отделка (нижний (яблочный_сорт)) = «вкусный красный»
 

В этом примере любое значение, например « RED DELICIOUS » или « Red Delicious », возвращает true при сравнении. Интересным моментом в запросе является вложенный вызов функций trim и lower : результат нижний вызов функции используется в качестве параметра функции подстройки .

Другие типичные текстовые функции: SUBSTRING , POSITION и REPLACE :

• SUBSTRING извлекает часть строки, указав начальную позицию и длину для извлечения .
• POSITION возвращает целое число, указывающее позицию подстроки в строке-контейнере.
• REPLACE() заменяет один символ другим. Вы можете указать несколько символов для замены.

Запрос в этом примере создает отчет, объясняющий эти три функции. Если вы не понимаете запрос, просмотрите результаты, а затем проверьте запрос еще раз.

выберите 'replace(''Белая лошадь'', ''h'', ''H'')' как function_call,
       "Белая лошадь" как оригинал,
        replace('Белая лошадь', 'h', 'H') как результат
 

Результат:

«replace(‘Белая лошадь’, ‘h’, ‘H’)»; «Белая лошадь»; «Белая лошадь»

выберите 'substring(''Белая лошадь'', 3, 5)' как function_call,
       "Белая лошадь" как оригинал,
       substring('Белая лошадь',3 , 5) как результат
 

Результат:

«подстрока(‘Белая лошадь’, 3, 5)»;«Белая лошадь»;«белая»

выберите 'position(''лошадь'' IN ''белая лошадь'')' как function_call,
       "Белая лошадь" как оригинал,
        position('лошадь' IN 'Белая лошадь') как результат
 

Результат:

Запросы используют различные текстовые функции SQL.