T-SQL Как преобразовать строку с разделителями-запятыми в целое число. Sql число в строку


sql-server - MS SQL server - преобразовать строку HEX в целое число

Этот ответ на тот же вопрос:

Преобразовать целое число в шестнадцатеричное и шестнадцатеричное целое

.. не работает для меня.

Я не могу перейти к строке HEX к целому числу, используя MS SQL server 2005 CAST или CONVERT. Я пропустил что-то тривиальное? Я искал экстенсивно, и лучшее, что я могу найти, - это длинные пользовательские функции, чтобы перейти от значения шестнадцатеричной строки к тому, что выглядит как десятичный int. Конечно, есть простой способ сделать это непосредственно в запросе, используя встроенные функции, а не писать пользовательскую функцию?

Спасибо

Изменить, чтобы включить примеры:

выберите CONVERT (INT, 0x89)

работает как ожидалось, но

выберите CONVERT (INT, '0x' + substring (msg, 66, 2)) из sometable

получает меня:

"Ошибка конверсии при преобразовании значения varchar '0x89' в тип данных int."

дополнительный явный CAST:

выберите CONVERT (INT, CAST ('0x89' AS VARBINARY))

выполняется, но возвращает 813185081.

Подстановка "Int", "Decimal" и т.д. для "Varbinary" приводит к ошибке. В общем случае строки, которые кажутся числовыми, интерпретируются как числовые, если это необходимо, но не в этом случае, и, похоже, CAST не распознает HEX. Я хотел бы думать, что есть что-то простое и очевидное, и я просто пропустил его.

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) 6 сентября 2009 г. 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Express Edition с расширенными службами в Windows NT 5.1 (сборка 2600: с пакетом обновления 3)

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

qaru.site

T-SQL Как преобразовать строку с разделителями-запятыми в целое число Безопасный SQL

Если LegalIssue содержит строку с запятыми, то вам действительно нужна таблица ассоциаций. Отсутствие этого, здесь удобный (но не эффективный) способ сделать соединение:

SELECT F.[FDTitle], E.PrimaryOpID as [FD Primary OP ID], F.County as [FD County], F.Status as [FD Status], F.IssueDate as [FD Date] FROM [dbo].[tbl_FinalDetMain] F LEFT OUTER JOIN [dbo].[tbl_lk_Exemptions_FD] E ON ','+F.LegalIssue+',' like '%,'cast(E.ID as varchar(255))+',%' WHERE F.[FDNbr] = '2013-0041';

Это добавляет и публикует список с запятыми, чтобы избежать конфликтов, например, найти «10» в «1 100 000».

Используя xml-тип данных, вы можете взорвать свою строку в целые числа, подобные этому. Хороший кандидат на пользовательскую функцию я бы сказал 🙂

declare @test varchar(max) set @test = '1,2,3,4,5' select T2.item.value('(./text())[1]','int') from (select convert(xml,'<items><t>'+replace(@test,',','</t><t>')+'</t></items>') as xmldoc) as xmltable CROSS APPLY xmltable.xmldoc.nodes('/items/t') as T2(item)

Вам придется либо нормализовать F.LegalIssue во множество строк, либо вам придется использовать LIKE

Что-то вроде

CAST(E.ID A VARCHAR(50)) = F.LegalIssue OR F.LegalIssue LIKE CAST(E.ID A VARCHAR(50)) + ',%' OR '%,' + F.LegalIssue LIKE CAST(E.ID A VARCHAR(50)) + ',%' OR '%,' + F.LegalIssue LIKE CAST(E.ID A VARCHAR(50))

Как вы можете видеть, актуальной конструкцией таблицы является проблема. Вы должны избегать дизайна, который у вас есть в настоящее время, и выбрать дизайн от одного 1 to many или many to many .

Вот ДЕМО, как сгладить значения с помощью рекурсивного CTE

SQL Fiddle DEMO

Создание тестовой таблицы и данных

CREATE TABLE Tada( ID INT, SomeCommaString VARCHAR(50) ) INSERT INTO Tada Values (1, '10'),(2,'5,6,12,16')

Сглаживание стола

;WITH Vals AS ( SELECT ID, CASE WHEN CHARINDEX(',',SomeCommaString) = 0 THEN SomeCommaString WHEN CHARINDEX(',',SomeCommaString) > 0 THEN LEFT(SomeCommaString,CHARINDEX(',',SomeCommaString) - 1) END Val, CASE WHEN CHARINDEX(',',SomeCommaString) > 0 THEN RIGHT(SomeCommaString,LEN(SomeCommaString) - CHARINDEX(',',SomeCommaString)) ELSE NULL END Remainder FROM Tada UNION ALL SELECT ID, CASE WHEN CHARINDEX(',',Remainder) = 0 THEN Remainder WHEN CHARINDEX(',',Remainder) > 0 THEN LEFT(Remainder,CHARINDEX(',',Remainder) - 1) END Val, CASE WHEN CHARINDEX(',',Remainder) > 0 THEN RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',',Remainder)) ELSE NULL END Remainder FROM Vals WHERE Remainder IS NOT NULL ) SELECT ID, Val FROM Vals

sql.fliplinux.com

sql - Как преобразовать число в числовую, отформатированную в запятую строку?

Я просмотрел несколько вариантов. Вот мои два фаворита, потому что мне нужно было округлить ценность.

,DataSizeKB = replace(convert(varchar,Cast(Round(SUM(BigNbr / 0.128),0)as money),1), '.00','') ,DataSizeKB2 = format(Round(SUM(BigNbr / 0.128),0),'##,##0') ----------------- --- below if the full script where I left DataSizeKB in both methods ----------- --- enjoy --------- --- Hank Freeman : [email protected] ----------------------------------- --- Scritp to get rowcounts and Memory size of index and Primary Keys SELECT FileGroupName = DS.name ,FileGroupType = CASE DS.[type] WHEN 'FG' THEN 'Filegroup' WHEN 'FD' THEN 'Filestream' WHEN 'FX' THEN 'Memory-optimized' WHEN 'PS' THEN 'Partition Scheme' ELSE 'Unknown' END ,SchemaName = SCH.name ,TableName = OBJ.name ,IndexType = CASE IDX.[type] WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' WHEN 2 THEN 'Nonclustered' WHEN 3 THEN 'XML' WHEN 4 THEN 'Spatial' WHEN 5 THEN 'Clustered columnstore' WHEN 6 THEN 'Nonclustered columnstore' WHEN 7 THEN 'Nonclustered hash' END ,IndexName = IDX.name ,RowCounts = replace(convert(varchar,Cast(p.rows as money),1), '.00','') --- MUST show for all types when no Primary key --,( Case WHEN IDX.[type] IN (2,6,7) then 0 else p.rows end )as Rowcounts_T ,AllocationDesc = AU.type_desc /* ,RowCounts = p.rows --- MUST show for all types when no Primary key ,TotalSizeKB2 = Cast(Round(SUM(AU.total_pages / 0.128),0)as int) -- 128 pages per megabyte ,UsedSizeKB = Cast(Round(SUM(AU.used_pages / 0.128),0)as int) ,DataSizeKB = Cast(Round(SUM(AU.data_pages / 0.128),0)as int) --replace(convert(varchar,cast(1234567 as money),1), '.00','') */ ,TotalSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.total_pages / 0.128),0)as money),1), '.00','') -- 128 pages per megabyte ,UsedSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.used_pages / 0.128),0)as money),1), '.00','') ,DataSizeKB = replace(convert(varchar,Cast(Round(SUM(AU.data_pages / 0.128),0)as money),1), '.00','') ,DataSizeKB2 = format(Round(SUM(AU.data_pages / 0.128),0),'##,##0') ,DataSizeKB3 = format(SUM(AU.data_pages / 0.128),'##,##0') --SELECT Format(1234567.8, '##,##0.00') --- ,is_default = CONVERT(INT,DS.is_default) ,is_read_only = CONVERT(INT,DS.is_read_only) FROM sys.filegroups DS -- you could also use sys.data_spaces LEFT JOIN sys.allocation_units AU ON DS.data_space_id = AU.data_space_id LEFT JOIN sys.partitions PA ON (AU.[type] IN (1,3) AND AU.container_id = PA.hobt_id) OR (AU.[type] = 2 AND AU.container_id = PA.[partition_id]) LEFT JOIN sys.objects OBJ ON PA.[object_id] = OBJ.[object_id] LEFT JOIN sys.schemas SCH ON OBJ.[schema_id] = SCH.[schema_id] LEFT JOIN sys.indexes IDX ON PA.[object_id] = IDX.[object_id] AND PA.index_id = IDX.index_id ----- INNER JOIN sys.partitions p ON obj.object_id = p.OBJECT_ID AND IDX.index_id = p.index_id WHERE OBJ.type_desc = 'USER_TABLE' -- only include user tables OR DS.[type] = 'FD' -- or the filestream filegroup GROUP BY DS.name ,SCH.name ,OBJ.name ,IDX.[type] ,IDX.name ,DS.[type] ,DS.is_default ,DS.is_read_only -- discard different allocation units ,p.rows ,AU.type_desc --- ORDER BY DS.name ,SCH.name ,OBJ.name ,IDX.name --- ;

qaru.site

MS SQL server - преобразовать строку HEX в целое число

Этот ответ на тот же вопрос:

Преобразовать целое число в hex и hex в integer

.. не работает для меня.

Я не могу перейти к строке HEX к целому числу, используя MS SQL Server 2005 CAST или CONVERT. Я пропустил что-то тривиальное? Я искал экстенсивно, и лучшее, что я могу найти, - это длинные пользовательские функции, чтобы перейти от значения шестнадцатеричной строки к тому, что выглядит как десятичный int. Конечно, есть простой способ сделать это непосредственно в запросе, используя встроенные функции, а не писать пользовательскую функцию?

благодаря

Изменить, чтобы включить примеры:

выберите CONVERT (INT, 0x89)

работает, как ожидалось, но

выберите CONVERT (INT, '0x' + substring (msg, 66, 2)) из sometable

получает меня:

«Ошибка конверсии при преобразовании значения varchar« 0x89 »в тип данных int».

дополнительный явный CAST:

выберите CONVERT (INT, CAST ('0x89' AS VARBINARY))

выполняется, но возвращает 813185081.

Подстановка «Int», «Decimal» и т. Д. Для «Varbinary» приводит к ошибке. В общем случае строки, которые кажутся числовыми, интерпретируются как числовые, если это необходимо, но не в этом случае, и, похоже, CAST не распознает HEX. Я хотел бы думать, что есть что-то простое и очевидное, и я просто пропустил это.

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) 6 сентября 2009 г. 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Express Edition с расширенными службами в Windows NT 5.1 (сборка 2600: с пакетом обновления 3)

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

Спасибо за то, что вы дали более явные примеры. Насколько я могу судить по документации и Google, это невозможно в MSSQL 2005 без UDF или другого процедурного кода. В MSSQL 2008 функция CONVERT () стиль  Параметр теперь поддерживает двоичные данные, поэтому вы можете сделать это прямо следующим образом:

select convert(int, convert(varbinary, '0x89', 1))

В предыдущих версиях ваш выбор:

  • Использовать UDF (TSQL или CLR; CLR может быть проще для этого)
  • Оберните SELECT в хранимой процедуре (но у вас, вероятно, все равно будет эквивалент UDF)
  • Преобразуйте его в интерфейс приложения
  • Переход на MSSQL 2008

Если преобразование данных предназначено только для показа, приложение может быть самым простым решением: форматирование данных обычно в любом случае принадлежит. Если вы должны сделать это в запросе, то UDF будет проще, но производительность может быть невелика (я знаю, вы сказали, что предпочитаете не использовать UDF, но непонятно почему). Я предполагаю, что обновление до MSSQL 2008 только для этого, вероятно, нереально.

Наконец, FYI номер версии, который вы включили, - это версия Management Studio, а не номер версии вашего сервера. Чтобы получить это, запросите сам сервер с помощью select @@versionили select serverproperty('ProductVersion'),

programmerz.ru

sql-server - Как создать случайное число для каждой строки в TSQL Select?

Взгляните на SQL Server - установите случайные числа на основе оснований, которые содержат очень подробное объяснение.

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

ABS(CHECKSUM(NewId())) % 14

Чтобы изменить диапазон, просто измените число в конце выражения. Будьте особенно внимательны, если вам нужен диапазон, который включает как положительные, так и отрицательные числа. Если вы сделаете это неправильно, можно удвоить число 0.

Небольшое предупреждение для математических гаек в комнате: в этом коде есть очень незначительная уклона. CHECKSUM() приводит к получению чисел, которые являются однородными во всем диапазоне типа данных sql Int или, по крайней мере, как можно ближе к моему тестированию (редактору). Тем не менее, будет некоторый уклон, когда CHECKSUM () произведет число в самом верхнем конце этого диапазона. Каждый раз, когда вы получаете число между максимально возможным целым числом и последним точным кратным размеру вашего желаемого диапазона (в этом случае 14), до этого максимального целого числа, эти результаты предпочтительнее, чем оставшаяся часть вашего диапазона, которая не может быть получена из что последний кратный 14.

В качестве примера, представьте, что весь диапазон типа Int составляет всего 19. 19 - это максимально возможное целое число, которое вы можете удерживать. Когда CHECKSUM () приводит к 14-19, они соответствуют результатам 0-5. Эти цифры будут в значительной степени благоприятствовать более 6-13, потому что CHECKSUM () в два раза чаще их генерирует. Проще продемонстрировать это визуально. Ниже приведен весь возможный набор результатов для нашего мнимого целочисленного диапазона:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Range Result: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 0 1 2 3 4 5

Вы можете видеть здесь, что есть больше шансов произвести некоторые цифры, чем другие: смещение. К счастью, фактический диапазон типа Int намного больше ... настолько, что в большинстве случаев смещение почти невозможно обнаружить. Тем не менее, это то, что нужно знать, если вы когда-либо делаете это для серьезного кода безопасности.

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

С SQL Server 2008 была введена новая функция CRYPT_GEN_RANDOM(8) , которая использует CryptoAPI для получения криптографически сильного случайного числа, возвращенного как VARBINARY(8000) . Вот страница документации: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

Поэтому, чтобы получить случайное число, вы можете просто вызвать функцию и применить ее к нужному типу:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

или чтобы получить float между -1 и +1, вы можете сделать что-то вроде этого:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0

code-examples.net

sql - SQL: выберите динамическое число строк в виде столбцов

Я однажды написал хранимую процедуру, которая сделала что-то вроде этого. Представлены таблица пользователей с основными сведениями и переменным количеством свойств профиля для пользователей. (Количество свойств профиля зависит от портала DotNetNuke)

Это прямо из DotNetNuke 4.9, проверьте схему базы данных, и вы получите подробную информацию. В число таблиц входят пользователи, UserPortals, UserProfile, ProfilePropertyDefinition

Короче говоря:

  • Я создаю временную таблицу со всеми свойствами профиля в виде столбцов (динамически)
  • Я заполняю таблицу temp с помощью userid (в качестве внешнего ключа для ссылки на таблицу пользователей и всех данных свойств профиля
  • Я присоединяюсь к таблице users и temp table

Это дает мне одну строку для каждого пользователя со всеми свойствами профиля.

Здесь код - не идеальный, но эй, его предназначенный для моих нужд, но должен быть достаточно легким для повторного использования (старался избегать курсоров btw)

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[rows2cols] @portalId INT AS BEGIN print 'PortalID=' + convert(varchar,@portalId) --SET NOCOUNT ON; declare @idx int declare @rowcount int declare @tmpStr nvarchar(max) declare @ctype nvarchar(max) declare @cname nvarchar(max) declare @clen int declare @createStr nvarchar(max) --------------------------------------------------------------------------- -- create tmp table -- --------------------------------------------------------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxx]') AND type in (N'U')) DROP TABLE [dbo].[xxxx] print 'Building Temp Table Cols for profile properties...' set @rowcount = (select count(*) from ProfilePropertyDefinition where PortalID=0 and deleted=0) set @idx = 1 set @tmpStr = '' while (@idx <= @rowcount) begin -- dynamically generate rownumbers to be able to do loop over them (avoid cursors) select @cname = t1.PropertyName from ( select ROW_NUMBER() OVER (ORDER BY ViewOrder) as Idx, PropertyName from ProfilePropertyDefinition where PortalID=0 and deleted=0 ) as t1 where t1.Idx = @idx if (@cname = 'Email' or @cname = 'FirstName' or @cname = 'LastName') begin set @clen = 1 end else begin set @tmpStr = @tmpStr + '[' + @cname + '] [nvarchar](500), ' end set @idx = @idx + 1 end set @tmpStr = @tmpStr + '[userid] [int] ' set @createStr = 'create table xxxx ( ' + @tmpStr + ' )' Print @createStr Exec (@createStr) --------------------------------------------------------------------------- -- fill tmp table -- --------------------------------------------------------------------------- declare @propName nvarchar(max) declare @propVal nvarchar(max) declare @userId int declare @idx2 int declare @rowcount2 int declare @inscol nvarchar(max) declare @insval nvarchar(max) set @rowcount = (select count(*) FROM Users LEFT OUTER JOIN UserPortals ON Users.UserID = UserPortals.UserId WHERE UserPortals.PortalId = @portalId) set @idx = 1 while (@idx <= @rowcount) begin -- get userId select @userId = t1.UserID from (select u.UserID, ROW_NUMBER() OVER (ORDER BY u.UserID) as Idx from Users as u LEFT OUTER JOIN UserPortals as up ON u.UserID = up.UserId where up.PortalId = @portalId) as t1 where t1.Idx = @idx set @idx2 = 1 set @rowcount2 = (select count(*) from UserProfile where UserID = @userId) set @inscol = '' set @insval = '' while (@idx2 < @rowcount2) begin -- build insert for a specific user select @propName = t1.PropertyName , @propVal=t1.PropertyValue from ( select ROW_NUMBER() OVER (ORDER BY ProfileID) as Idx, up.PropertyDefinitionID,ppd.PropertyName, up.PropertyValue from UserProfile as up inner join ProfilePropertyDefinition as ppd on up.PropertyDefinitionID = ppd.PropertyDefinitionID where UserID = @userId ) as t1 where t1.Idx = @idx2 if (@propName != 'Firstname' and @propName != 'LastName' and @propName != 'Email') begin set @inscol = @inscol + @propName + ', ' set @insval = @insval + 'N''' + replace(@propVal,'''','''''') + ''', ' end set @idx2 = @idx2 + 1 end set @inscol = @inscol + 'userid' set @insval = @insval + convert(nvarchar,@userId) set @tmpStr = 'insert into xxxx (' + @inscol + ') values (' + @insval + ')' --print @tmpStr Exec(@tmpStr) set @idx = @idx + 1 end -- ------------------------------------------------------------------------- -- return tmp table & dump -- -- ------------------------------------------------------------------------- SELECT Users.*, xxxx.* FROM xxxx INNER JOIN Users ON xxxx.userid = Users.UserID IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxx]') AND type in (N'U')) DROP TABLE [dbo].[xxxx] END

qaru.site