Как преобразовать значения TIMESTAMP в VARCHAR в T-SQL, как это делает SSMS?

Я пытаюсь преобразовать поле TIMESTAMP в таблице в строку, чтобы ее можно было распечатать или выполнить как часть динамического SQL. SSMS может это сделать, поэтому для этого должен быть встроенный метод. Однако я не могу заставить его работать с помощью T-SQL.

Следующее правильно отображает результат таблицы:

SELECT TOP 1 RowVersion FROM MyTable

Он показывает 0x00000000288D17AE. Однако мне нужно, чтобы результат был частью большей строки.

DECLARE @res VARCHAR(MAX) = (SELECT TOP 1 'test' + CONVERT(BINARY(8), RowVersion) FROM MyTable)
PRINT(@res)

Выдает ошибку: The data types varchar and binary are incompatible in the add operator

DECLARE @res VARCHAR(MAX) = (SELECT TOP 1 'test' + CONVERT(VARCHAR(MAX), RowVersion) FROM MyTable)
PRINT(@res)

Это приводит к мусорным символам: test (®

На самом деле пробелы — это просто нулевые символы, которые завершают строку для выполнения динамического SQL с использованием EXEC().

DECLARE @sql VARCHAR(MAX) = 'SELECT TOP 1 ''test'' + CONVERT(VARCHAR(MAX), RowVersion) FROM MyTable'
EXEC (@sql)

Это просто отображает результат таблицы со словом «тест». Все после «теста» в динамическом SQL обрезается, потому что функция CONVERT сначала возвращает завершающие нулевые символы.

Очевидно, что я хочу, чтобы результирующая строка была «test0x00000000288D17AE» или даже десятичным эквивалентом, который в данном случае будет «test680335278».

Любые идеи очень приветствуются.


sql-server tsql ssms sql-timestamp nvarchar
person Neo    schedule 07.12.2016    source источник
comment
SSMS не выполняет такого преобразования. Оно отображает данные в сетке так же, как любое другое приложение .NET. Двоичные значения обычно отображаются в шестнадцатеричном формате или в формате Base64. Почему вы вообще хотите преобразовать двоичное значение в строку? Почему бы не передать его как есть или использовать как параметр? Кстати, rowversion также нельзя использовать в качестве идентификатора, если вы думали, что можете использовать его как удобочитаемый ключ.   -  person Panagiotis Kanavos    schedule 07.12.2016
comment
rowversion — это значение binary(8), как и bigint. Вы можете привести его к bigint, а затем использовать FORMAT, чтобы получить его шестнадцатеричное представление, например FORMAT(cast(RowVersion)as bigint),"x") или FORMAT(cast(cast(RowVersion as binary(8))as bigint),"x"). Однако формат не будет заполнять строку нулями   -  person Panagiotis Kanavos    schedule 07.12.2016
comment
Хотя это недокументировано, я считаю, что master.sys.fn_varbintohexstr преобразует любое значение типа VARBINARY в значение NVARCHAR. См. здесь некоторую информацию о том, как работает эта функция, а также исходный код.   -  person 3N1GM4    schedule 07.12.2016

Ответы (3)


arrow_upward
26
arrow_downward

SELECT 'test' + CONVERT(NVARCHAR(MAX), CONVERT(BINARY(8), RowVersion), 1). Хитрость заключается в том, что 1 соответствует CONVERT в качестве стиля, согласно документации. (Пропустите 2, чтобы пропустить 0x.)

person Jeroen Mostert    schedule 07.12.2016
comment
Вы пробовали это? Он возвращает Explicit conversion from data type timestamp to nvarchar(max) is not allowed. Если я изменю его на VARCHAR, ошибки не будет, но все равно будет отображаться мусор, как описано в Вопросе. - person Neo; 07.12.2016
comment
@Neo: Плохо, я неправильно предположил, что SQL Server будет обрабатывать ROWVERSION как двоичный тип. Это не так, поэтому для этого необходим промежуточный этап преобразования. - person Jeroen Mostert; 07.12.2016
comment
Принятый ответ - лучшее решение, я думаю. SELECT TOP 1 'test' + master.sys.fn_varbintohexstr(RowVersion) FROM MyTable дает мне именно то, что мне нужно. - person Neo; 07.12.2016
comment
@Neo: полагаться на недокументированные функции явно хуже, чем полагаться на встроенную функциональность CONVERT. Что вы принимаете, конечно, зависит от вас, но я знаю, что я предпочел бы в производственной среде. - person Jeroen Mostert; 07.12.2016
comment
@Neo Вы пробовали отредактированную версию решения Jeroen Mostert? У меня это работает с дополнительным шагом CONVERT сначала к BINARY(8), а затем CONVERT к NVARCHAR... Таким образом, и по причинам, указанным выше Jeroen, я проголосовал за этот ответ, так как считаю, что он лучше, чем мой собственный. - person 3N1GM4; 07.12.2016
comment
Ладно, на этот раз плохо. Да, отредактированная версия работает. На самом деле, теперь я предпочитаю этот ответ, поскольку шестнадцатеричные буквы остаются в верхнем регистре (выглядит лучше). Это не производственный код (это просто скрипт, который помогает мне в разработке), но я предпочитаю делать все правильно, и я думаю, что это все. Я изменю принятый ответ на этот, но все же поддержу ваш, @ 3N1GM4. Спасибо за честность! - person Neo; 07.12.2016
comment
@Neo, нет проблем, всегда рад признать, что у кого-то есть лучший ответ, чем у меня! - person 3N1GM4; 07.12.2016

arrow_upward
3
arrow_downward

Как упоминалось в комментариях, недокументированная функция master.sys.fn_varbintohexstr будет преобразовывать двоичный код в строку, чтобы вы могли затем конкатенировать с каким-либо другим строковым значением:

DECLARE @binary BINARY(8)
SELECT @binary = CAST(1234567890 AS BINARY(8))

SELECT @binary AS BinaryValue, 
       LEFT(master.sys.fn_varbintohexstr(@binary),2) + UPPER(RIGHT(master.sys.fn_varbintohexstr(@binary),LEN(master.sys.fn_varbintohexstr(@binary))-2)) AS VarcharValue,
       'test' + LEFT(master.sys.fn_varbintohexstr(@binary),2) + UPPER(RIGHT(master.sys.fn_varbintohexstr(@binary),LEN(master.sys.fn_varbintohexstr(@binary))-2)) AS ConcatenatedVarcharValue

Я пошел дальше и разделил первые два символа и не применил к ним функцию UPPER, чтобы точно воспроизвести формат, отображаемый при двоичном значении.

Полученные результаты:

/--------------------------------------------------------------------\
|     BinaryValue    |    VarcharValue    | ConcatenatedVarcharValue |
|--------------------+--------------------+--------------------------|
| 0x00000000499602D2 | 0x00000000499602D2 |  test0x00000000499602D2  |
\--------------------------------------------------------------------/
person 3N1GM4    schedule 07.12.2016
comment
Спасибо. SELECT TOP 1 'test' + master.sys.fn_varbintohexstr(RowVersion) FROM MyTable дает мне именно то, что мне нужно. - person Neo; 07.12.2016

arrow_upward
1
arrow_downward

Посмотри на это:

SELECT 
substring(replace(replace(replace(replace(cast(CAST(GETDATE() AS datetime2) as 
varchar(50)),'-',''),' ',''),':',''),'.',''),1,18)
person daniel    schedule 10.10.2017
comment
Добавьте объяснение к ответу, почему это работает. - person foxyblue; 11.10.2017