sql - 从字符串转换为uniqueidentifier sql时转换失败
问题描述
我创建了一个具有以下属性的表:
CREATE TABLE [a02].[property]
(
propertyID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT newsequentialid(),
propertyTitle VARCHAR(100) NOT NULL,
residenceName VARCHAR(100) NOT NULL,
residenceStreet VARCHAR(100) NOT NULL,
postCode VARCHAR(100) NOT NULL,
bedroomCount INT NOT NULL,
bathroomCount INT NOT NULL,
garageCarCount INT NOT NULL,
internalSqMetres FLOAT NOT NULL,
outdoorSqMetres FLOAT NOT NULL,
askingPrice FLOAT NOT NULL,
sellingPrice FLOAT,
registeredDate DATE NOT NULL,
soldDate DATE,
localityID UNIQUEIDENTIFIER NOT NULL
REFERENCES [a02].[locality] (localityID),
typeID UNIQUEIDENTIFIER NOT NULL
REFERENCES [a02].[propertyType] (typeID),
accountID UNIQUEIDENTIFIER NOT NULL
REFERENCES [a02].[userAccount] (accountID)
);
我正在插入以下记录:
INSERT INTO [a02].[property] (propertyTitle, residenceName, residenceStreet,
postCode, bedroomCount, bathroomCount, garageCarCount,
internalSqMetres, outdoorSqMetres, askingPrice, sellingPrice,
registeredDate, soldDate, localityID, typeID, accountID)
VALUES ('Ground Floor Maisonette Fgura', '10', 'Triq il-Kbira',
'FGR2000', 3, 1.5, 2,
130, 50, 250000, 235000,
'11/13/2018', '11/13/2018',
(SELECT localityName FROM [a02].[locality]
WHERE localityName = 'Il-Fgura'),
(SELECT typeName FROM [a02].[propertyType]
WHERE typeName ='Maisonette'),
(SELECT accountID FROM [a02].[userAccount]
WHERE accountUsername = 'joeb')),
('First Floor Floor Maisonette Fgura', '10', 'Triq il-Kbira',
'FGR2000', 3, 1.5, 2,
130, 50, 350000, 335000, '11/13/2018', '11/13/2017',
(SELECT localityName FROM [a02].[locality]
WHERE localityName = 'Il-Fgura'),
(SELECT typeName FROM [a02].[propertyType]
WHERE typeName = 'Maisonette'),
(SELECT accountID FROM [a02].[userAccount]
WHERE accountUsername ='joeb')),
('Ground Floor Maisonette Mosta', '20', 'Triq il-Qamh',
'MST1000', 4, 2, 2,
150, 50, 275000, NULL, '11/13/2018', NULL,
(SELECT localityName FROM [a02].[locality]
WHERE localityName = 'Il-Mosta'),
(SELECT typeName FROM [a02].[propertyType]
WHERE typeName = 'Maisonette'),
(SELECT accountID FROM [a02].[userAccount]
WHERE accountUsername = 'lisaA')),
('Penthouse Zabbar', '5', 'Triq il-Passa',
'ZBR1050', 3, 1, 1,
150, 50, 300000, NULL, '11/13/2018', NULL,
(SELECT localityName FROM [a02].[locality]
WHERE localityName = 'Haz-Zabbar'),
(SELECT typeName FROM [a02].[propertyType]
WHERE typeName = 'Penthouse'),
(SELECT accountID FROM [a02].[userAccount]
WHERE accountUsername ='joeb'));
我收到此错误:
从字符串转换为唯一标识符时转换失败
它标志着这条线:
INSERT INTO [a02].[property] (propertyTitle, residenceName,residenceStreet, postCode, bedroomCount, bathroomCount, garageCarCount, internalSqMetres, outdoorSqMetres, askingPrice, sellingPrice, registeredDate, soldDate, localityID, typeID, accountID)
任何线索可能是什么?
解决方案
在插入语句中使用以下查询将字符串值转换为唯一标识符。
字符串值应该 >= 33。如果 > 33,它只需要 33 个字符
SELECT CAST(
SUBSTRING(<ColName>, 1, 8) + '-' + SUBSTRING(<ColName>, 9, 4) + '-' + SUBSTRING(<ColName>, 13, 4) + '-' +
SUBSTRING(<ColName>, 17, 4) + '-' + SUBSTRING(<ColName>, 21, 12)
AS UNIQUEIDENTIFIER)
它会像
INSERT INTO [a02].[property] (
propertyTitle
, residenceName
, residenceStreet
, postCode
, bedroomCount
, bathroomCount
, garageCarCount
, internalSqMetres
, outdoorSqMetres
, askingPrice
, sellingPrice
, registeredDate
, soldDate
, localityID
, typeID
, accountID
)
VALUES (
'Ground Floor Maisonette Fgura'
, '10'
, 'Triq il-Kbira'
, 'FGR2000'
, 3
, 1.5
, 2
, 130
, 50
, 250000
, 235000
, '11/13/2018'
, '11/13/2018'
, (
SELECT CAST(
SUBSTRING(localityName, 1, 8) + '-' + SUBSTRING(localityName, 9, 4) + '-' + SUBSTRING(localityName, 13, 4) + '-' +
SUBSTRING(localityName, 17, 4) + '-' + SUBSTRING(localityName, 21, 12)
AS UNIQUEIDENTIFIER)
FROM [a02].[locality]
WHERE localityName = 'Il-Fgura'
)
, (
SELECT CAST(
SUBSTRING(typeName, 1, 8) + '-' + SUBSTRING(typeName, 9, 4) + '-' + SUBSTRING(typeName, 13, 4) + '-' +
SUBSTRING(typeName, 17, 4) + '-' + SUBSTRING(typeName, 21, 12)
AS UNIQUEIDENTIFIER)
FROM [a02].[propertyType]
WHERE typeName = 'Maisonette'
)
, (
SELECT CAST(
SUBSTRING(accountID, 1, 8) + '-' + SUBSTRING(accountID, 9, 4) + '-' + SUBSTRING(accountID, 13, 4) + '-' +
SUBSTRING(accountID, 17, 4) + '-' + SUBSTRING(accountID, 21, 12)
AS UNIQUEIDENTIFIER)
FROM [a02].[userAccount]
WHERE accountUsername = 'joeb'
)
)
推荐阅读
- javascript - React Material UI Checkbox:如何通过选中不同的框来选中/取消选中组中的其他框?
- php - 在带有 CI 的 crud 示例中显示 404 错误页面
- fonts - 在 Google Docs 中使用连字字体?
- python - 用另一个数据帧中的值替换数据帧中的值 - 正则表达式
- android - 构建失败并出现异常,评估项目“:app”时出现问题。> ionic cordova 项目的 java.lang.ExceptionInInitializerError
- node.js - 等到在 NodeJs 中触发事件
- php - 有没有办法使用 Google 搜索 API 来查找 Web 2.0 死链接?
- javascript - 添加自定义 JavaScript 时功能停止工作
- tinymce - 在tinymce中上传图像标签的问题
- typescript - 如何指定类型约束以在两个谓词中具有相同的参数?