我有一个CSV文件，其中包含我要批量导入到sql server表中的IPv6地址。CSV文件中的地址示例可能是“ 558282959301147147248235258744685685608464”。我希望将IP地址存储到数据库中的两个bigint中，因为SQL bigint只能处理64位，而IPv6地址需要128位。我用于处理IPV4地址的导入代码是：
SET @sql = '
INSERT INTO dbo.IPCountry
SELECT
REPLACE(ipFROM, ''"'', '''') AS ipFROM,
REPLACE(ipTO, ''"'', '''') AS ipTO,
CAST(REPLACE(countrySHORT, ''"'', '''') AS CHAR(2)) AS countrySHORT
FROM OPENROWSET(
BULK ''' + @Directory + 'IPCountry.csv'',
FORMATFILE = ''' + @Directory + 'IPCountry.Xml''
) AS t1
';
exec sp_executesql @sql;
如何将128位IPv6值转换为两个bigint值？
根据您的示例，它是一个38位数的数字字符串，我将其分成两半，并将每一半都转换为BIGINT组件。
这是一个演示该过程并将其逆转的示例：
-- Prepare test variables
DECLARE @Source AS NVARCHAR(38)
DECLARE @ipFROMa AS BIGINT
DECLARE @ipFROMb AS BIGINT
DECLARE @Destination AS NVARCHAR(38)
SET @Source = '55828295930114724823525874468560830464'
-- Split Source into TWO strings and then each into a BIGINT
SET @ipFROMa = (SELECT CAST(SUBSTRING(@Source,1,19) AS BIGINT))
SET @ipFROMb = (SELECT CAST(SUBSTRING(@Source,20,19) AS BIGINT))
-- Test returning BIGINTs into one IPv6 string
SET @Destination = (SELECT
CAST(
CAST(@ipFROMa AS NVARCHAR(19)) +
CAST(@ipFROMb AS NVARCHAR(19))
AS nvarchar(38)))
-- Display the values for verification
SELECT @Source, @ipFROMa, @ipFROMb, @Destination
结果：
55828295930114724823525874468560830464 5582829593011472482 3525874468560830464 55828295930114724823525874468560830464
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句