CODEWITHSUNDEEP
sqlsql-server
Jake Miller
Jake Miller

Reputation: 2642

Attempt to cast a column only if certain criteria is met

My company uses media management software which stores all metadata for media in 1 table (dimensions, titles, captions, tags, etc...). Because of this, all values are of type VARCHAR. It's not a huge problem because each row has a field called MetaName which defines what type of data exists in that row. For instance, MetaName 1 is the title field and MetaName 3 is height in pixels.

I'm attempting to find images between certain dimensions in pixels. Since the values are stored as type VARCHAR such as 1200px, I need to replace the px and cast to an INT before I test a size range like so: CAST(REPLACE(VALUE, 'px', '') AS INT).

Here's a simplified schema and an example to easier understand what I'm talking about:

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 ''
);

And test values + my query:

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)

This should work fine, however, the CAST call fails because the query tries to cast ALL of the Value columns so when it runs into "Title1" or "Description1", an error occurs.

How can I cast ONLY on fields with MetaName = 3 or 4? I figured the MetaName = 3 and the MetaName = 4 at the beginning of the conditional statements before the AND would prevent the CAST + REPLACE from executing but it happens on all columns regardless.

Upvotes: 3

Views: 206

Answers (3)

S3S
S3S

Reputation: 25122

You can use try_convert on SQL Server 2012 onward

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)

Or 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)

Or, you can wrap that cast in a 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)

Or, use 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)

Or, break up the filtering using a 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

Upvotes: 3

kiran gadhe
kiran gadhe

Reputation: 743

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

Upvotes: 2

NikhilC
NikhilC

Reputation: 41

What you can do is use TRY_CAST instead of 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)

When I do this it only returns MetaNames 3 and 4 as part of the result set. I believe this functionality was introduced in SQL Server 2012.

Upvotes: 2

Related Questions