Разница между различными типами строк в SQL Server? Sql строки
Функции работы со строками в ms sql server 2005
Вот полный перечень функций работы со строками, взятый из BOL:
| ASCII | NCHAR | SOUNDEX |
| CHAR | PATINDEX | SPACE |
| CHARINDEX | REPLACE | STR |
| DIFFERENCE | QUOTENAME | STUFF |
| LEFT | REPLICATE | SUBSTRING |
| LEN | REVERSE | UNICODE |
| LOWER | RIGHT | UPPER |
| LTRIM | RTRIM |
Начнем с двух взаимно обратных функций - ASCII и CHAR.
Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.
Вот, например, как можно определить, сколько имеется разных букв, с которых начинаются названия кораблей в таблице Ships:
| SELECT COUNT(DISTINCT ASCII(name)) FROM Ships |
Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):
| SELECT DISTINCT CHAR(ASCII(name)) FROM Ships ORDER BY 1 |
Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции - LEFT, которая имеет следующий синтаксис:
LEFT (<строковое выражение>, <целочисленное выражение> )
и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,
| SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1 |
А вот как, например, можно получить таблицу кодов всех алфавитных символов:
| SELECT CHAR(ASCII('a')+ num-1) letter, ASCII('a')+ num - 1 [code] FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z ) x WHERE ASCII('a')+ num -1 BETWEEN ASCII('a') AND ASCII('z') |
Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .
Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
| UNION SELECT CHAR(ASCII('A')+ num-1) letter, ASCII('A')+ num - 1 [code] FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z ) x WHERE ASCII('A')+ num -1 BETWEEN ASCII('A') AND ASCII('Z') |
Чтобы таблица выглядела более патриотично, достаточно заменить латинские буквы "a" и "A" на неотличимые на взгляд русские - "а" и "А", а "z" и "Z" на "я" и "Я". Вот только буквы "ё" вы не увидите в этой таблице, т.к. в кодовой таблице ASCII эти символы лежат отдельно, что легко проверить:
| SELECT ASCII('ё') UNION ALL SELECT ASCII('Ё') |
Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.
Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX. Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:
CHARINDEX (искомое_выражение, строковое_выражение[, стартовая_позиция])
Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск искомого_выражения . Если этот параметр опущен, поиск выполняется от начала строкового_выражения. Например, запрос
| SELECT name FROM Ships WHERE CHARINDEX('sh', name) > 0 |
будет выводить те корабли, в которых имеется сочетание символов "sh". Здесь используется тот факт, что если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие корабли:
| name |
| Kirishima |
| Musashi |
| Washington |
Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"
| SELECT CHARINDEX('a',name) first_a, CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a FROM Ships WHERE name='California' |
Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX('a', name)+1. Правильность результата - 2 и 10 - легко проверить :-).
Функция PATINDEX имеет синтаксис:
PATINDEX ('%образец%' , строковое_выражение)
Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид
| SELECT name FROM Ships WHERE PATINDEX('%sh%', name) > 0 |
А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть "e":
| SELECT name FROM Ships WHERE PATINDEX('%e_e%', name) >0 |
Результат выполнения этого запроса выглядит следующим образом:
| name |
| Revenge |
| Royal Sovereign |
Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:
RIGHT(<строковое выражения>,<число символов>)
Вот, например, как можно определить имена кораблей, которые начинаются и заканчиваются на одну и ту же букву:
| SELECT name FROM Ships WHERE LEFT(name, 1) = RIGHT(name, 1) |
То, что в результате мы получим пустой результирующий набор, означает, что таких кораблей в базе данных нет. Давайте возьмем комбинацию значений - класс и имя корабля.
Соединение двух строковых значений в одно называется конкатенацией, и в SQL Server для этой операции используется знак "+" (в стандарте "||"). Итак,
| SELECT * FROM ( SELECT class +' '+ name AS cn FROM Ships ) x WHERE LEFT(cn, 1)=RIGHT(cn, 1) |
Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:
| cn |
| Iowa Missouri |
| North Carolina Washington |
А если строковое выражение будет содержать лишь одну букву? Запрос выведет ее. В этом легко убедиться, написав
| SELECT * FROM ( SELECT class +' '+ name AS cn FROM Ships UNION ALL SELECT 'a' as nc ) x WHERE LEFT(cn, 1)=RIGHT(cn, 1) |
Чтобы исключить этот случай, можно воспользоваться еще одной полезной функцией LEN (<строковое выражение>), которая возвращает число символов в строке. Ограничимся случаем, когда число символов больше единицы:
| SELECT * FROM ( SELECT class +' '+ name AS cn FROM Ships UNION ALL SELECT 'a' as nc ) x WHERE LEFT(cn, 1)=RIGHT(cn, 1) AND LEN(cn)>1 |
Замечание. Реализация этой функции в MS SQL Server имеет одну особенность, а именно, при подсчете длины не учитываются концевые пробелы. Действительно, выполним следующий код:
| DECLARE @chr AS CHAR(12), @vchr AS VARCHAR(12) SELECT @chr = 'abcde' + REPLICATE(' ', 5), @vchr = 'abcde'+REPLICATE(' ', 5) SELECT LEN(@chr), LEN(@vchr) SELECT DATALENGTH(@chr), DATALENGTH(@vchr) |
Функция REPLICATE дополняет константу 'abcde' пятью пробелами справа, которые не учитываются функцией LEN, - в обоих случаях получаем 5. Функция DATALENGTH возвращает число байтов в представлении переменной и демонстрирует нам различие между типами CHAR и VARCHAR. DATALENGTH даст нам 12 для типа CHAR и 10 - для VARCHAR. Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернула фактическую длину переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR - это тип фиксированной длины. Если значение переменной оказывается меньше ее длины, а длину мы объявили как CHAR(12), то значение переменной будет "выровнено" до требуемой длины за счет добавления концевых пробелов.
На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т.д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете ("2d") или скорость CD ("24x"). Проблема заключается в том, что текст сортируется так (по возрастанию)
Действительно,
| SELECT '1a' AS place UNION ALL SELECT '2a' UNION ALL SELECT '11a' ORDER BY 1 |
Если же требуется упорядочить места в порядке возрастания рядов, то порядок должен быть такой
Чтобы добиться такого порядка, нужно выполнить сортировку по числовым значениям, присутствующим в тексте. Можно предложить такой алгоритм: 1. Извлечь число из строки. 2. Привести его к числовому формату. 3. Выполнить сортировку по приведенному значению.
Т.к. нам известно, что буква только одна, то для извлечения числа из строки можно воспользоваться следующей конструкцией, которая не зависит от числа цифр в номере места:
| LEFT(place, LEN(place)-1) |
Если только этим и ограничиться, то получим
Приведение к числовому формату может быть следующим:
| CAST (LEFT(place, LEN(place)-1) AS INT) |
Осталось выполнить сортировку
| SELECT * FROM ( SELECT '1a' AS place UNION ALL SELECT '2a' UNION ALL SELECT '11a' ) x ORDER BY CAST(LEFT(place, LEN(place)-1) AS INT) |
Что и требовалось доказать.
Ранее мы для извлечения числа из текстовой строки пользовались функцией LEFT, т.к. нам было известно априори, какое число символов нужно убрать справа (один). А если же нужно извлечь строку из подстроки не по известной позиции символа, а по самому символу? Например: извлечь все символы до первой буквы "х" (значение скорости CD).
В этом случае мы можем использовать также уже рассмотренную ранее функцию CHARINDEX, которая позволит определить неизвестную позицию символа:
| SELECT model, LEFT(cd, CHARINDEX('x', cd) -1) FROM PC |
studfiles.net
sql - Самый быстрый способ найти строку подстрокой в SQL?
Вы можете добавить еще один вычисленный столбец в таблице: titleLength как len (title) PERSISTED. Это сохранит длину столбца "title". Создайте индекс для этого.
Кроме того, добавьте еще один вычисленный столбец с именем: ReverseTitle в качестве обратного (название) PERSISTED.
Теперь, когда кто-то ищет ключевое слово, проверьте, совпадает ли длина ключевого слова с именем titlelength. Если да, выполните поиск "=". Если длина ключевого слова меньше длины titleLength, тогда сделайте LIKE. Но сначала сделайте заголовок LIKE 'abc%', затем сделайте reverseTitle LIKE 'cba%'. Подобно подходу Brad - т.е. Вы выполняете следующий сложный запрос только в случае необходимости.
Кроме того, если правила 80-20 применяются к вашим ключевым словам/подстрокам (т.е. если большая часть поисковых запросов находится на меньшем количестве ключевых слов), вы также можете рассмотреть возможность сделать какое-то кэширование. Например, скажем, вы обнаружите, что многие пользователи ищут ключевое слово "abc", и поиск этого ключевого слова возвращает записи с идентификаторами 20, 22, 24, 25 - вы можете сохранить это в отдельной таблице и индексировать. И теперь, когда кто-то ищет новое ключевое слово, сначала загляните в эту таблицу "cache", чтобы узнать, был ли поиск уже выполнен более ранним пользователем. Если это так, не нужно снова смотреть в основной таблице. Просто верните результаты из таблицы "cache".
Вы также можете комбинировать вышеуказанное с SQL Server TextSearch. (при условии, что у вас есть веская причина не использовать его). Но вы, тем не менее, можете использовать текстовый поиск, чтобы вкратце установить результат. а затем запустить SQL-запрос к вашей таблице, чтобы получить точные результаты, используя идентификаторы, возвращаемые TExt-поиском, в качестве параметра вместе с вашим ключевым словом.
Все это, очевидно, предполагает, что вам нужно использовать SQL. Если нет, вы можете изучить что-то вроде Apache Solr.
qaru.site
И снова о парсинге строк в SQL
В одном из своих предыдущих постов я уже приводил пример как распарсить строку. Теперь хочу поделиться еще одним способом парсинга строки:DECLARE @string NVARCHAR(10) = 'a,b,c'
DECLARE @delimiter NVARCHAR(1) = ',';
WITH ToTable([START], [END]) AS (
SELECT 1 AS [START]
,CAST(CHARINDEX(@delimiter, @string) AS INT) AS [END]
UNION ALL
SELECT CAST([END] + 1 AS INT)
,CAST(CHARINDEX(@delimiter, @string, [END] + 1) AS INT)
FROM ToTable
WHERE [END] > 0
)
SELECT SUBSTRING(@string, [START],
CASE WHEN [END] > 0 THEN [END] - [START] ELSE LEN(@string) END) AS [Words]
FROM ToTable
Полезные ссылки- CTE (Common Table Expressions)- функции для работы со строками
maximus-sql-notes.blogspot.com
+ (объединение строк) (Transact-SQL)
ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse
Оператор в строковом выражении, объединяющий две или более символьных или двоичных строки, два или более столбцов или несколько строк и имен столбцов в одно выражение (строковый оператор). Например SELECT 'book'+'case'; возвращает bookcase.
Синтаксические обозначения в Transact-SQL
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse expression + expressionвыражениеЛюбое допустимое выражение какой-либо один из данных типов в символьных и двоичных данных категории типов, за исключением изображение, ntext, или текст типов данных. Оба выражения должны иметь одинаковый тип данных, или одно из выражений должно допускать неявное преобразование к типу данных другого выражения.
При сцеплении двоичных строк с любыми символами между двоичными строками необходимо использовать явное преобразование в символьные данные. В следующем примере показано, когда CONVERT, или CAST, должны использоваться с двоичным объединением и когда CONVERT, или CAST, не должен использоваться.
DECLARE @mybin1 varbinary(5), @mybin2 varbinary(5) SET @mybin1 = 0xFF SET @mybin2 = 0xA5 SELECT @mybin1 + @mybin2 SELECT CONVERT(varchar(5), @mybin1) + ' ' + CONVERT(varchar(5), @mybin2) SELECT CAST(@mybin1 AS varchar(5)) + ' ' + CAST(@mybin2 AS varchar(5))Возвращает тип данных аргумента с самым высоким приоритетом. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
При работе с пустыми строками нулевой длины оператор + (объединение строк) ведет себя иначе, чем при работе со значениями NULL или с неизвестными значениями. Символьная строка символа нулевой длины может быть указана в виде двух одинарных кавычек без каких-либо символов между ними. Двоичная строка нулевой длины может быть указана как 0x без указания каких-либо байтовых значений в шестнадцатеричной константе. При сцеплении строки нулевой длины всегда сцепляются две указанные строки. При работе со строками со значением NULL результат объединения зависит от настроек сеанса. При присоединении нулевого значения к известному значению результатом будет неизвестное значение, объединение строк с нулевым значением также дает нулевое значение, как и в арифметических действиях с нулевыми значениями. Однако это поведение можно изменить, изменив значение CONCAT_NULL_YIELDS_NULL для текущего сеанса. Дополнительные сведения см. в разделе SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
Если результат объединения строк превышает предел в 8 000 байт, то он усекается. Однако усечения не произойдет, если хотя бы одна из сцепляемых строк принадлежит к типу больших значений.
A. Использование объединения строк
В следующем примере создается единственный столбец с заголовком Name из нескольких символьных столбцов, где за фамилией лица следуют запятая, один пробел и имя того же лица. Результирующий набор сортируется в алфавитном порядке по возрастанию, сначала по фамилии, а затем по имени.
SELECT (LastName + ', ' + FirstName) AS Name FROM Person.Person ORDER BY LastName ASC, FirstName ASC;Б. Объединение числовых типов данных и дат
В следующем примере используется CONVERT функцию для объединения числовое и даты типов данных.
SELECT 'The order is due on ' + CONVERT(varchar(12), DueDate, 101) FROM Sales.SalesOrderHeader WHERE SalesOrderID = 50001; GOНиже приводится результирующий набор.
------------------------------------------------
The order is due on 04/23/2007
(1 row(s) affected)
В. Использование объединения нескольких строк
Следующий пример сцепляет несколько строк в одну длинную строку для отображения фамилии и первой буквы инициалов вице-президентов в Компания Adventure Works Cycles. После фамилии ставится запятая, а после первой буквы инициалов — точка.
SELECT (LastName + ',' + SPACE(1) + SUBSTRING(FirstName, 1, 1) + '.') AS Name, e.JobTitle FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle LIKE 'Vice%' ORDER BY LastName ASC; GOНиже приводится результирующий набор.
Name Title
------------- ---------------
Duffy, T. Vice President of Engineering
Hamilton, J. Vice President of Production
Welcker, B. Vice President of Sales
(3 row(s) affected)
Г. Использование больших строк в объединения
Следующий пример Сцепляет несколько строк в одну длинную строку, а затем пытается вычислить длину окончательную строку. Окончательный длина результирующего набора является 16000, так как оценка выражения начинается из левой, @x + @z + @y => (@x + @z) + @y. В этом случае результат (@x + @z) будет усечен до 8000 байт и затем @y добавляется результирующего набора, что делает окончательную строку длиной 16000. Поскольку @y является строкой типа больших значений, усечения не произойдет.
DECLARE @x varchar(8000) = replicate('x', 8000) DECLARE @y varchar(max) = replicate('y', 8000) DECLARE @z varchar(8000) = replicate('z',8000) SET @y = @x + @z + @y -- The result of following select is 16000 SELECT len(@y) AS y GOНиже приводится результирующий набор.
y
-------
16000
(1 row(s) affected)
Д. Использование объединения строк
В следующем примере создается один столбец под заголовком столбца Name из нескольких символьных столбцов, фамилии контактного лица следуют запятая, пробел и имя контактного лица. Результирующий набор сортируется в алфавитном порядке по возрастанию, сначала по фамилии, а затем по имени.
SELECT (LastName + ', ' + FirstName) AS Name FROM DimEmployee ORDER BY LastName ASC, FirstName ASC;Е. Использование объединения нескольких строк
Следующий пример Сцепляет несколько строк в одну длинную строку для отображения фамилии и первой буквы инициалов вице-президентов в образце базы данных. После фамилии ставится запятая, а после первой буквы инициалов — точка.
SELECT (LastName + ', ' + SUBSTRING(FirstName, 1, 1) + '.') AS Name, Title FROM DimEmployee WHERE Title LIKE '%Vice Pres%' ORDER BY LastName ASC;Ниже приводится результирующий набор.
Name Title Duffy, T. Vice President of Engineering Hamilton, J. Vice President of Production Welcker, B. Vice President of SalesALTER DATABASE (Transact-SQL) Функции CAST и CONVERT (Transact-SQL) Преобразование типов данных (ядро СУБД) Типы данных (Transact-SQL) Выражения (Transact-SQL)Встроенные функции (Transact-SQL) Операторы (Transact-SQL) SELECT (Transact-SQL) Инструкции SET (Transact-SQL) += (Объединение строк) (Transact-SQL)
technet.microsoft.com
sql - Строки разделения SQL
Похоже, вы хотите, чтобы следующий последний экземпляр OU=. Вот как вы можете это сделать.
declare @table table (c1 varchar(256)) insert into @table values ('CN=ABCDEFG,OU=7SOE,OU=TEMP,OU=Sydney,OU=Australia,DC=LDAP,DC=COM'), ('CN=ABCDEFGHIJ,OU=7SOEFHU,OU=TEST,OU=TEMP,OU=London,OU=Europe,DC=LDAP,DC=COM') select right(left(c1, len(c1) - charindex('UO',reverse(c1)) - 2),charindex('UO',reverse(left(c1, len(c1) - charindex('UO',reverse(c1)) - 2))) - 2) from @tableПросто замените c1 и @table на название столбца и имя таблицы, соответственно.
Если вам нужен гладкий способ разделить материал, вот функция, которая будет делать то, что очень быстро, благодаря Джеффу Модену.
CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN /* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... enough to cover VARCHAR(8000)*/ WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l ; GOВот как вы могли бы использовать его в своей ситуации:
select t.c1 ,x.ItemNumber --this represents the item number in the string. ,x.Item --this is the value of that item ,RN = row_number() over (partition by c1 order by x.ItemNumber desc) from @table t cross apply dbo.DelimitedSplit8K(c1,',') x --where -- left(x.Item,2) = 'OU' --uncomment this out to see how the RN will be used in the CTE below order by t.c1, x.ItemNumberИтак, чтобы получить только row_number() результаты, мы применили бы row_number() только к тем элементам, которые начинаются с OU и заказывают их. Таким образом, row_number(), что = 2 (рядом с последним), будет значением, которое вы хотите.
;with cte as( select * ,RN = row_number() over (partition by c1 order by x.ItemNumber desc) from @table cross apply dbo.DelimitedSplit8K(c1,',') x where left(x.Item,2) = 'OU') select c1, YourExtract = right(Item,len(Item) - 3) from cte where RN = 2qaru.site
string - Разница между различными типами строк в SQL Server?
text и ntext устарели, поэтому опускайте их на мгновение. Для того, что осталось, есть 3 измерения:
- Unicode (UCS-2) и не-unicode: N перед именем обозначает Unicode
- Фиксированная длина против переменной длины: var обозначает переменную, в противном случае фиксированную
- В строке или BLOB: (max) как длина обозначает BLOB, в противном случае это значение в строке
Таким образом, вы можете прочитать любое значение типа:
- CHAR(10): это фиксированная длина строки, не являющаяся Unicode размером 10
- NVARCHAR(256): это строка переменной длины Unicode размером до 256
- VARCHAR(MAX): это переменная длина BLOB не-Unicode
Устаревшие типы text и ntext соответствуют новым типам VARCHAR(MAX) и nvarchar(max) соответственно.
Когда вы переходите к деталям, значение in-row vs. BLOB размывается для небольших длин, так как движок может оптимизировать хранилище и вытаскивать строку BLOB или вставлять значение строки в "маленький BLOB ', но это всего лишь деталь реализации. См. Таблица и индексная организация.
С точки зрения программирования все типы: CHAR, VARCHAR, NCHAR, NVARCHAR, VARCHAR(MAX) и nvarchar(max) поддерживают единую строку API: Строковые функции. Старые, устаревшие типы text и ntext не поддерживают этот API, у них есть отдельный, отложенный, TEXT API для управления. Вы не должны использовать устаревшие типы.
Типы BLOB поддерживают эффективные обновления на месте с помощью синтаксиса UPDATE table SET column.WRITE(@value, @offset).
Разница между типами фиксированной длины и переменной длины исчезает при сжатии строк в таблице. С включенным сжатием строк фиксированные длины и длина переменной сохраняются в одном формате, а конечные пробелы не сохраняются на диске, см. Внедрение сжатия строк, Обратите внимание, что сжатие страницы подразумевает сжатие строк.
qaru.site
sql - Разбор строки SQL
declare @s varchar(max) = 'I=940;A=29.5;D=20090901|I=941;A=62.54;D=20090910|I=942;A=58.99;D=20091005|I=954;A=93.45;D=20091201|I=944;A=96.76;D=20091101|I=946;A=52.5;D=20091101|I=943;A=28.32;D=20091101|I=945;A=52.5;D=20091101|I=955;A=79.81;D=20091201|I=950;A=25.2;D=20091124|I=948;A=31.86;D=20091110|I=949;A=28.32;D=20091120|I=947;A=25.2;D=20091109|I=951;A=242.54;D=20091124|I=952;A=28.32;D=20091129|I=956;A=38.94;D=20091210|I=957;A=107.39;D=20091215|I=958;A=32.55;D=20091228|I=959;A=27.3;D=20091228|I=960;A=24.79;D=20091230|I=1117;A=28.32;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209' declare @xml xml select @xml = '<item><value>'+replace(replace(@s, ';','</value><value>'), '|','</value></item><item><value>')+'</value></item>' select N.value('substring(value[1],3)', 'int') as Invoice, N.value('substring(value[2],3)', 'money') as Amount, N.value('substring(value[3],3)', 'date') as [Date] from @xml.nodes('item') as T(N)Результат:
Invoice Amount Date ----------- --------------------- ---------- 940 29,50 2009-09-01 941 62,54 2009-09-10 942 58,99 2009-10-05 954 93,45 2009-12-01 944 96,76 2009-11-01 946 52,50 2009-11-01 943 28,32 2009-11-01 945 52,50 2009-11-01 955 79,81 2009-12-01 950 25,20 2009-11-24 948 31,86 2009-11-10 949 28,32 2009-11-20 947 25,20 2009-11-09 951 242,54 2009-11-24 952 28,32 2009-11-29 956 38,94 2009-12-10 957 107,39 2009-12-15 958 32,55 2009-12-28 959 27,30 2009-12-28 960 24,79 2009-12-30 1117 28,32 2010-01-31 1115 272,58 2010-01-31 1116 159,60 2010-02-09Для SQL Server 2005 вам нужно использовать datetime вместо даты
select N.value('substring(value[1],3)', 'int') as Invoice, N.value('substring(value[2],3)', 'money') as Amount, N.value('substring(value[3],3)', 'datetime') as [Date] from @xml.nodes('item') as T(N)Для чтения из таблицы вам нужно сделать это следующим образом.
declare @s varchar(max) = 'I=940;A=29.5;D=20090901|I=941;A=62.54;D=20090910|I=942;A=58.99;D=20091005|I=954;A=93.45;D=20091201|I=944;A=96.76;D=20091101|I=946;A=52.5;D=20091101|I=943;A=28.32;D=20091101|I=945;A=52.5;D=20091101|I=955;A=79.81;D=20091201|I=950;A=25.2;D=20091124|I=948;A=31.86;D=20091110|I=949;A=28.32;D=20091120|I=947;A=25.2;D=20091109|I=951;A=242.54;D=20091124|I=952;A=28.32;D=20091129|I=956;A=38.94;D=20091210|I=957;A=107.39;D=20091215|I=958;A=32.55;D=20091228|I=959;A=27.3;D=20091228|I=960;A=24.79;D=20091230|I=1117;A=28.32;D=20100131|I=1115;A=272.58;D=20100131|I=1116;A=159.6;D=20100209' declare @YourTable table(ID int, s varchar(max)) insert into @YourTable values (1, @s), (2, @s) select Y.ID, T.N.value('substring(value[1],3)', 'int') as Invoice, T.N.value('substring(value[2],3)', 'money') as Amount, T.N.value('substring(value[3],3)', 'date') as [Date] from @YourTable as Y cross apply (select cast('<item><value>'+replace(replace(Y.s, ';','</value><value>'), '|','</value></item><item><value>')+'</value></item>' as xml)) as X(XMLCol) cross apply X.XMLCol.nodes('item') as T(N)qaru.site
