February 14, 2013 at 1:40 pm
Hi,
I'm new to T-SQL and need a little help with this query.
I need to convert or CAST and INT into a Char inside a case stament. I'm going to be updating another table with this and I need it to change 0 into M but I can't quite get the convert right.
SELECT CASE
WHEN MWBS = 1 THEN 1
WHEN MWBS = 2 THEN 2
WHEN MWBS = 0 THEN CONVERT(CHAR(1), MWBS ,'M')
ELSE NULL
END
FROM dbo.MyTable
Thanks,
February 14, 2013 at 1:49 pm
scribesjeff (2/14/2013)
Hi,I'm new to T-SQL and need a little help with this query.
I need to convert or CAST and INT into a Char inside a case stament. I'm going to be updating another table with this and I need it to change 0 into M but I can't quite get the convert right.
SELECT CASE
WHEN MWBS = 1 THEN 1
WHEN MWBS = 2 THEN 2
WHEN MWBS = 0 THEN CONVERT(CHAR(1), MWBS ,'M')
ELSE NULL
END
FROM dbo.MyTable
Thanks,
Perhaps this:
SELECT CASE
WHEN MWBS = 1 THEN '1'
WHEN MWBS = 2 THEN '2'
WHEN MWBS = 0 THEN 'M'
ELSE NULL
END
FROM dbo.MyTable
February 14, 2013 at 3:01 pm
Hi Lynn,
Thanks for the reply but when you are getting data that is stored as an int and you are trying to case it as a char that will error out with a data conversion error. I was looking to convert it to char as it will be in the destination table.
Thanks,
February 14, 2013 at 3:12 pm
THANKS LYNN! I missed the quotes 8-(
Works good now.
February 14, 2013 at 7:00 pm
Just to demonstrate something, consider again Lynn's example which does an implicit conversion.
DECLARE @MWBS INT, @Result CHAR(1)
SET @MWBS = 0
SELECT
@Result =
(CASE
WHEN @MWBS = 1 THEN '1'
WHEN @MWBS = 2 THEN '2'
WHEN @MWBS = 0 THEN 'M'
ELSE NULL
END)
SELECT @Result
It will also work the other way around because these statements are passing constants.
DECLARE @MWBS1 CHAR(1), @Result1 INT
SET @MWBS1 = 'M'
SELECT
@Result1 =
(CASE
WHEN @MWBS1 = '1' THEN 1
WHEN @MWBS1 = '2' THEN 2
WHEN @MWBS1 = 'M' THEN 0
ELSE NULL
END)
SELECT @Result1
But your original question was a good one. Sometimes you do need to do an
explicit cast/convert in a case statement or you will get an error. Try this
example which uses a variable:
DECLARE @MWBS2 VARCHAR(10), @Result2 DECIMAL(6,3)
SET @MWBS2 = '1.987'
-- this one works due to the cast
SELECT
@Result2 =
(CASE
WHEN NULLIF(@MWBS2,'0') IS NOT NULL
THEN CAST(@MWBS2 AS DECIMAL(6,3))
WHEN @MWBS2 = '0'
THEN 0
ELSE
NULL
END)
-- this one fails because it's trying to implicitly convert
-- the varchar variable to a decimal
SELECT
@Result2 =
(CASE
WHEN NULLIF(@MWBS2,'0') IS NOT NULL THEN @MWBS2
WHEN @MWBS2 = '0' THEN 0
ELSE NULL
END)
SELECT @Result2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply