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

замечания

Оставил

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

Параметры:

1. символьное выражение. ntext выражение может быть любого типа данных, который может быть неявно преобразован в varchar или nvarchar , за исключением text или ntext
2. максимальная длина. Целое число от 0 bigint максимального значения maxint (9,223,372,036,854,775,807).
   Если параметр максимальной длины отрицательный, будет поднята ошибка.

SELECT LEFT('This is my string', 4) -- result: 'This'

Если максимальная длина больше количества символов в строке, возвращается строка entier.

SELECT LEFT('This is my string', 50) -- result: 'This is my string'

Правильно

Возвращает вспомогательную строку, которая является самой правой частью строки, с указанной максимальной длиной.

Параметры:

1. символьное выражение. ntext выражение может быть любого типа данных, который может быть неявно преобразован в varchar или nvarchar , за исключением text или ntext
2. максимальная длина. Целое число от 0 bigint максимального значения maxint (9,223,372,036,854,775,807). Если параметр максимальной длины отрицательный, будет поднята ошибка.

SELECT RIGHT('This is my string', 6) -- returns 'string'

Если максимальная длина больше количества символов в строке, возвращается строка entier.

SELECT RIGHT('This is my string', 50) -- returns 'This is my string'

Substring

Возвращает подстроку, которая начинается с символа, который указан в указанном начальном индексе и заданной максимальной длине.

Параметры:

1. Выражение символов. ntext выражение может быть любого типа данных, который может быть неявно преобразован в varchar или nvarchar , за исключением text или ntext .
2. Начать индекс. Число ( int или bigint ), которое указывает начальный индекс запрашиваемой подстроки. ( Примечание: строки в sql-сервере являются индексом base 1, что означает, что первым символом строки является индекс 1). Это число может быть меньше 1. В этом случае, если сумма начального индекса и максимальной длины больше 0, возвращаемой строкой будет строка, начинающаяся с первого символа символьного выражения и с длиной (начальный индекс + максимальная длина - 1). Если оно меньше 0, пустая строка будет возвращена.
3. Максимальная длина. Целое число от 0 bigint максимального значения maxint (9,223,372,036,854,775,807). Если параметр максимальной длины отрицательный, будет поднята ошибка.

SELECT SUBSTRING('This is my string', 6, 5) -- returns 'is my'

Если максимальная длина + начальный индекс больше количества символов в строке, возвращается строка entier.

SELECT SUBSTRING('Hello World',1,100) -- returns 'Hello World'

Если начальный индекс больше, чем число символов в строке, возвращается пустая строка.

SELECT SUBSTRING('Hello World',15,10) -- returns ''

ASCII

Возвращает значение int, представляющее код ASCII самого левого символа строки.

SELECT ASCII('t') -- Returns 116
SELECT ASCII('T') -- Returns 84
SELECT ASCII('This') -- Returns 84

Если строка является Unicode, а самый левый символ не ASCII, а представлен в текущей сортировке, может быть возвращено значение больше 127:

SELECT ASCII(N'ï') -- returns 239 when `SERVERPROPERTY('COLLATION') = 'SQL_Latin1_General_CP1_CI_AS'`

Если строка является Unicode, а самый левый символ не может быть представлен в текущей сортировке, возвращается значение int 63 (которое представляет знак вопроса в ASCII):

SELECT ASCII(N'߷') -- returns 63
SELECT ASCII(nchar(2039)) -- returns 63

CHARINDEX

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

Список параметров:

1. Строка для поиска (до 8000 символов)
2. Строка для поиска (любой допустимый тип и длина символьных данных, включая двоичный)
3. (Необязательно) для начала. Число типов int или большой int. Если пропущено или меньше 1, поиск начинается с начала строки.

Если строка поиска является varchar(max) , nvarchar(max) или varbinary(max) , то CHARINDEX функция возвращает bigint значение. В противном случае он вернет int .

SELECT CHARINDEX('is', 'this is my string') -- returns 3
SELECT CHARINDEX('is', 'this is my string', 4) -- returns 6
SELECT CHARINDEX(' is', 'this is my string') -- returns 5

голец

Возвращает символ, представленный кодом ASCII.

SELECT CHAR(116) -- Returns 't'
SELECT CHAR(84)  -- Returns 'T'

Это можно использовать для введения новой строки / строки CHAR(10) , возврата каретки CHAR(13) и т. Д. См. AsciiTable.com для справки.

Если значение аргумента не находится между 0 и 255, функция CHAR возвращает NULL .
Тип возвращаемых данных функции CHAR - char(1)

Len

Возвращает количество символов строки.
Примечание: функция LEN игнорирует конечные пробелы:

SELECT LEN('My string'), -- returns 9
       LEN('My string   '), -- returns 9
       LEN('   My string') -- returns 12

Если требуется длина, включая конечные пробелы, существует несколько способов достижения этого, хотя у каждого есть свои недостатки. Один из методов заключается в том, чтобы добавить один символ в строку, а затем использовать LEN минус один:

DECLARE @str varchar(100) = 'My string   '
SELECT LEN(@str + 'x') - 1 -- returns 12

Недостатком этого является то, что тип строковой переменной или столбца имеет максимальную длину, добавление дополнительного символа отбрасывается, а результирующая длина все равно не будет считать конечные пробелы. Чтобы решить эту проблему, следующая модифицированная версия решает проблему и дает правильные результаты во всех случаях за счет небольшого количества дополнительного времени выполнения, и из-за этого (правильные результаты, в том числе с суррогатными парами и разумной скоростью выполнения) представляется лучшей методикой для использования:

SELECT LEN(CONVERT(NVARCHAR(MAX), @str) + 'x') - 1

Другой метод - использовать функцию DATALENGTH .

DECLARE @str varchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 12

Важно отметить, что DATALENGTH возвращает длину в байтах строки в памяти. Это будет отличаться для varchar vs. nvarchar .

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 24

Вы можете отрегулировать это, разделив длину datalength на длину datalength одного символа (который должен быть одного типа). Пример ниже делает это, а также обрабатывает случай, когда целевая строка оказывается пустой, что позволяет избежать деления на ноль.

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) / DATALENGTH(LEFT(LEFT(@str, 1) + 'x', 1)) -- returns 12

Однако даже это имеет проблемы в SQL Server 2012 и выше. Это приведет к неправильным результатам, когда строка содержит суррогатные пары (некоторые символы могут занимать больше байтов, чем другие символы в одной строке).

Другой способ - использовать REPLACE для преобразования пробелов в непространственный символ и взять LEN результата. Это дает правильные результаты во всех случаях, но имеет очень низкую скорость выполнения с длинными строками.

Concat

SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'

SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
       'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.

SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'

DECLARE @Age INT=23;
SELECT CONCAT('Ram is ', @Age,' years old');  -- returns 'Ram is 23 years old'

SELECT 'This is the number ' + CAST(42 AS VARCHAR(5)) --returns 'This is the number 42'

ниже

Возвращает выражение символа ( varchar или nvarchar ) после преобразования всех символов верхнего регистра в нижний регистр.

Параметры:

1. Выражение символов. Любое выражение символов или двоичных данных, которые могут быть неявно преобразованы в varchar .

SELECT LOWER('This IS my STRING') -- Returns 'this is my string'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT LOWER(@String) -- Returns 'this is my string'

верхний

Возвращает выражение символа ( varchar или nvarchar ) после преобразования всех строчных символов в верхний регистр.

Параметры:

1. Выражение символов. Любое выражение символов или двоичных данных, которые могут быть неявно преобразованы в varchar .

SELECT UPPER('This IS my STRING') -- Returns 'THIS IS MY STRING'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT UPPER(@String) -- Returns 'THIS IS MY STRING'

LTrim

Возвращает выражение символа ( varchar или nvarchar ) после удаления всех ведущих пробелов, т. varchar nvarchar слева от первого символа пробела.

Параметры:

1. символьное выражение. Любое выражение символов или двоичных данных, которые могут быть неявно преобразованы в varcher , кроме text , ntext и image .

SELECT LTRIM('    This is my string') -- Returns 'This is my string'

RTrim

Возвращает выражение символа ( varchar или nvarchar ) после удаления всех завершающих пробелов, т. Е. Пробелы с правого конца строки до тех пор, пока не останется первый небелый пробел.

Параметры:

1. символьное выражение. Любое выражение символов или двоичных данных, которые могут быть неявно преобразованы в varcher , кроме text , ntext и image .

SELECT RTRIM('This is my string     ') -- Returns 'This is my string'

Unicode

Возвращает целочисленное значение, представляющее значение Unicode для первого символа входного выражения.

Параметры:

1. Unicode выражение символов. Любое действительное выражение nchar или nvarchar .

SELECT UNICODE(N'Ɛ') -- Returns 400

DECLARE @Unicode nvarchar(11) = N'Ɛ is a char'
SELECT UNICODE(@Unicode) -- Returns 400

NCHAR

Возвращает символы Unicode ( nchar(1) или nvarchar(2) ), соответствующие целочисленному аргументу, который он получает, как определено стандартом Unicode.

Параметры:

1. целочисленное выражение. Любое целочисленное выражение, которое является положительным числом от 0 до 65535, или если сопоставление базы данных поддерживает флаг дополнительного символа (CS), поддерживаемый диапазон находится в диапазоне от 0 до 1114111. Если целочисленное выражение не попадает внутрь этого диапазона, значение null равно вернулся.

SELECT NCHAR(257) -- Returns 'ā'
SELECT NCHAR(400) -- Returns 'Ɛ'

Задний ход

Возвращает строковое значение в обратном порядке.

Параметры:

1. строковое выражение. Любая строка или двоичные данные, которые могут быть неявно преобразованы в varchar .

Select REVERSE('Sql Server') -- Returns 'revreS lqS'

PATINDEX

Возвращает начальную позицию первого вхождения указанного шаблона в указанном выражении.

Параметры:

1. шаблон. Выражение символов содержит последовательность, которую нужно найти. Ограничено Максимальная длина 8000 символов. Подстановочные знаки ( % , _ ) могут использоваться в шаблоне. Если шаблон не начинается с шаблона, он может соответствовать только тому, что находится в начале выражения. Если он не заканчивается подстановочным знаком, он может соответствовать только тому, что находится в конце выражения.

2. выражение. Любой строковый тип данных.

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3. 

SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3. 

SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start. 

SELECT PATINDEX('inter%', 'interesting') -- Returns 1. 

SELECT PATINDEX('%ing', 'interesting') -- Returns 9. 

Космос

Возвращает строку ( varchar ) повторяющихся пробелов.

Параметры:

1. целочисленное выражение. Любое целое выражение, до 8000. Если отрицательный, возвращается null . если 0, возвращается пустая строка. (Чтобы вернуть строку длиной более 8000 пробелов, используйте Replicate.

SELECT SPACE(-1) -- Returns NULL
SELECT SPACE(0)  -- Returns an empty string
SELECT SPACE(3)  -- Returns '   ' (a string containing 3 spaces)

копировать

Повторяется строковое значение определенное количество раз.

Параметры:

1. строковое выражение. Строковое выражение может быть символьной строкой или двоичными данными.
2. целочисленное выражение. Любой целочисленный тип, включая bigint . Если отрицательный, возвращается null . Если 0, возвращается пустая строка.

SELECT REPLICATE('a', -1)  -- Returns NULL

SELECT REPLICATE('a', 0)  -- Returns ''

SELECT REPLICATE('a', 5)  -- Returns 'aaaaa'

SELECT REPLICATE('Abc', 3) -- Returns 'AbcAbcAbc'

Примечание. Если строковое выражение не относится к типу varchar(max) или nvarchar(max) , возвращаемое значение не будет превышать 8000 символов. Репликация прекратится до добавления строки, которая приведет к превышению этого значения:

SELECT LEN(REPLICATE('a b c d e f g h i j k l', 350)) -- Returns 7981

SELECT LEN(REPLICATE(cast('a b c d e f g h i j k l' as varchar(max)), 350)) -- Returns 8050

замещать

Возвращает строку ( varchar или nvarchar ), где все вхождения указанной подстроки заменяются другой подстрокой.

Параметры:

1. строковое выражение. Это строка, которую будет искать. Это может быть тип символов или двоичных данных.
2. шаблон. Это подстрока, которая будет заменена. Это может быть тип символов или двоичных данных. Аргумент шаблона не может быть пустой строкой.
3. замена. Это подстрока, которая заменит подстроку шаблона. Это может быть символ или двоичные данные.

SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.

Заметки:

• Если строковое выражение не относится к типу varchar(max) или nvarchar(max) , функция replace усекает возвращаемое значение в 8000 символов.
• Тип возвращаемых данных зависит от типов входных данных - возвращает nvarchar если одно из входных значений - nvarchar , или varchar противном случае.
• Возвращает NULL если любой из входных параметров NULL

String_Split

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

value
-----
Lorem
ipsum
dolor
sit
amet.

ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130

улица

Возвращает символьные данные ( varchar ), преобразованные из числовых данных.

Параметры:

1. float выражение. Примерный числовой тип данных с десятичной точкой.
2. длина. необязательный. Общая длина возвращаемого строкового выражения, включая цифры, десятичную точку и ведущие пробелы (при необходимости). Значение по умолчанию - 10.
3. десятичный. необязательный. Число цифр справа от десятичной точки. Если выше 16, результат будет усечен до шестнадцати мест справа от десятичной точки.

SELECT STR(1.2) -- Returns '         1'

SELECT STR(1.2, 3) -- Returns '  1'

SELECT STR(1.2, 3, 2) -- Returns '1.2'

SELECT STR(1.2, 5, 2) -- Returns ' 1.20'

SELECT STR(1.2, 5, 5) -- Returns '1.200'

SELECT STR(1, 5, 2) -- Returns ' 1.00'

SELECT STR(1) -- Returns '         1'

Quotename

Возвращает строку Unicode, окруженную разделителями, чтобы сделать ее допустимым идентификатором с разделителем SQL Server.

Параметры:

1. символьная строка. Строка данных Unicode, до 128 символов ( sysname ). Если входная строка длиннее 128 символов, функция возвращает null .
2. циферблат. Необязательно . Один символ для использования в качестве разделителя. Может быть одинарная кавычка ( ' или ``), левая или правая скобка ( { , [ , ( , < или > , ) , ] , } ) или двойная кавычка ( " ). Любое другое значение возвращает null Значение по умолчанию - квадратные скобки.

SELECT QUOTENAME('what''s my name?')      -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '[') -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', ']') -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '''') -- Returns 'what''s my name?'

SELECT QUOTENAME('what''s my name?', '"') -- Returns "what's my name?"

SELECT QUOTENAME('what''s my name?', ')') -- Returns (what's my name?)
SELECT QUOTENAME('what''s my name?', '(') -- Returns (what's my name?)

SELECT QUOTENAME('what''s my name?', '<') -- Returns <what's my name?>
SELECT QUOTENAME('what''s my name?', '>') -- Returns <what's my name?>

SELECT QUOTENAME('what''s my name?', '{') -- Returns {what's my name?}
SELECT QUOTENAME('what''s my name?', '}') -- Returns {what's my name?}

SELECT QUOTENAME('what''s my name?', '`') -- Returns `what's my name?`

Саундэкс

Возвращает четырехсимвольный код ( varchar ) для оценки фонетического сходства двух строк.

Параметры:

1. символьное выражение. Алфавитно-цифровое выражение символьных данных.

Функция soundex создает четырехсимвольный код, основанный на том, как будет звучать выражение символов при произнесении. первый символ - это верхний регистр первого символа параметра, остальные 3 символа - это числа, представляющие буквы в выражении (за исключением a, e, i, o, u, h, w и y, которые игнорируются) ,

SELECT SOUNDEX ('Smith') -- Returns 'S530'

SELECT SOUNDEX ('Smythe') -- Returns 'S530'

разница

Возвращает целочисленное ( int ) значение, которое указывает разницу между значениями soundex двух выражений символов.

Параметры:

1. символьное выражение 1.
2. символьное выражение 2.

Оба параметра представляют собой буквенно-цифровые выражения символьных данных.

Возвращаемое целое число - это количество символов в значениях soundex одинаковых параметров, поэтому 4 означает, что выражения очень похожи, а 0 означает, что они очень разные.

SELECT  SOUNDEX('Green'),  -- G650
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Green','Greene') -- Returns 4
        
SELECT  SOUNDEX('Blotchet-Halls'),  -- B432
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Blotchet-Halls', 'Greene') -- Returns 0

Формат

DECLARE @d DATETIME = '2016-07-31';  

SELECT 
    FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2016'
   ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2016'
   ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2016'
   ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2016/7/31'
   ,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Sunday, July 31, 2016'
   ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2016'
   ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Sonntag, 31. Juli 2016'

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2016'
      ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789',
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Sunday, July 31, 2016 12:00:00 AM'
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns रविवार, जुलाई 31, 2016 12:00:00 पूर्वाह्न
      ,FORMAT ( @d, 'dddd', 'en-US' )  AS 'US' -- Returns 'Sunday'
      ,FORMAT ( @d, 'dddd', 'hi-IN' )  AS 'Hindi' -- Returns 'रविवार'

DECLARE @Price1 INT = 40
SELECT FORMAT(@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'      
       ,FORMAT(@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default', -- Returns '$40.36'
       FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
       FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
       FORMAT( @Price, 'C2') AS 'With 2 Decimal', -- Returns '$40.36'

   DECLARE @Percentage float = 0.35674
   SELECT FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
   FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
   FORMAT( @Percentage, 'P1') AS '% with 1 Decimal'  -- Returns '35.7 %'

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA', -- Returns '4,54,545.39'
FORMAT( @Number, '#.0')     AS 'With 1 Decimal', -- Returns '454545.4'
FORMAT( @Number, '#.00')    AS 'With 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '000454545'

Category         Type             .Net type
-------------------------------------------
Numeric          bigint           Int64
Numeric          int              Int32
Numeric          smallint         Int16
Numeric          tinyint          Byte
Numeric          decimal          SqlDecimal
Numeric          numeric          SqlDecimal
Numeric          float            Double
Numeric          real             Single
Numeric          smallmoney       Decimal
Numeric          money            Decimal
Date and Time    date             DateTime
Date and Time    time             TimeSpan
Date and Time    datetime         DateTime
Date and Time    smalldatetime    DateTime
Date and Time    datetime2        DateTime
Date and Time    datetimeoffset   DateTimeOffset

String_escape

SELECT STRING_ESCAPE('\   /  
\\    "     ', 'json') -- returns '\\\t\/\n\\\\\t\"\t'

Special character    Encoded sequence
-------------------------------------
Quotation mark (")   \"
Reverse solidus (\)  \\
Solidus (/)          \/
Backspace            \b
Form feed            \f
New line             \n
Carriage return      \r
Horizontal tab       \t


Control character    Encoded sequence
------------------------------------
CHAR(0)            \u0000
CHAR(1)            \u0001
...                ...
CHAR(31)           \u001f