sql - 仅在满足特定条件时才尝试转换专栏

Question

我的公司使用媒体管理软件，该软件将媒体的所有元数据存储在 1 个表中(尺寸、标题、标题、标签等)。因此，所有值都是 VARCHAR 类型。这不是一个大问题，因为每一行都有一个名为 MetaName 的字段，它定义了该行中存在的数据类型。例如，MetaName 1 是标题字段，MetaName 3 是以像素为单位的高度。

我正在尝试查找特定尺寸(以像素为单位)之间的图像。由于值存储为 VARCHAR 类型，例如 1200px，我需要替换 px 并转换为 INT 在我像这样测试大小范围之前:CAST(REPLACE(VALUE, 'px', '') AS INT)。

这是一个简化的模式和一个示例，可以更容易地理解我在说什么:

CREATE TABLE Media (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  ImagePath VARCHAR(MAX)
);

CREATE TABLE MetaData (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  MediaId INT REFERENCES Media(ID),
  MetaName INT NOT NULL,
  Value VARCHAR(MAX) NOT NULL DEFAULT ''
);

测试值+我的查询:

INSERT INTO Media (ImagePath)
VALUES
('C:/mypath/myimage.png'), 
('C:/mypath/otherimage.jpg');

INSERT INTO MetaData (MediaId, MetaName, Value)
VALUES 
(1, 1, 'Title1'), (2, 1, 'Title2'),
(1, 2, 'Description1'), (2, 2, 'Description2'),
(1, 3, '1260px'), (2, 3, '1100px'),
(1, 4, '800px'), (2, 4, '1900px');

SELECT * FROM MetaData
WHERE 
 (MetaName = 3 AND CAST(REPLACE(Value, 'px', '') AS INT) BETWEEN 800 AND 1200)
 OR
 (MetaName = 4 AND CAST(REPLACE(Value, 'px', '') AS INT) BETWEEN 800 AND 1200)

这应该可以正常工作，但是，CAST 调用失败，因为查询试图转换所有值列，因此当它遇到“Title1”或“Description1”时，会发生错误。

如何仅在 MetaName = 3 或 4 的字段上进行转换？我认为 MetaName = 3 和 MetaName = 4 在 AND 之前的条件语句开头会阻止 CAST + REPLACE 从执行但无论如何它发生在所有列上。

Answer 1

您可以在 SQL Server 2012 及更高版本上使用 try_convert

SELECT * FROM MetaData
WHERE 
 (MetaName = 3 AND try_convert(int,REPLACE(Value, 'px', '')) BETWEEN 800 AND 1200)
 OR
 (MetaName = 4 AND try_convert(int,REPLACE(Value, 'px', '')) BETWEEN 800 AND 1200)

或者try_cast

SELECT * FROM MetaData
WHERE 
 (MetaName = 3 AND try_cast(REPLACE(Value, 'px', '') as int) BETWEEN 800 AND 1200)
 OR
 (MetaName = 4 AND try_cast(REPLACE(Value, 'px', '') as int) BETWEEN 800 AND 1200)

或者，您可以将该转换包装在 CASE

中

SELECT * FROM MetaData
where
(MetaName = 3 AND case when MetaName = 3 then CAST(REPLACE(Value, 'px', '') AS INT) end BETWEEN 800 AND 1200)
 OR
(MetaName = 4 AND case when MetaName = 4 then CAST(REPLACE(Value, 'px', '') AS INT) end BETWEEN 800 AND 1200)

或者，使用 IIF

SELECT * FROM MetaData
where
(MetaName = 3 AND iif(MetaName = 3,CAST(REPLACE(Value, 'px', '') AS INT),null) BETWEEN 800 AND 1200)
 OR
(MetaName = 4 AND iif(MetaName = 4,CAST(REPLACE(Value, 'px', '') AS INT),null) BETWEEN 800 AND 1200)

或者，使用 CTE 分解过滤。

;with cte as(
SELECT * FROM MetaData
where MetaName in (3,4))

select *
from cte
where CAST(REPLACE(Value, 'px', '') AS INT) BETWEEN 800 AND 1200