May 31, 2013 at 7:58 am
This shouldn't be that hard.
My situation. Working with addresses on a map. Some of the addresses will be the full length. 4 or 5 characters that are numbers but reside in a varchar column. One column contains the number of characters(numbers) I want presented. For example, think of a row of townhouses. I want both ends to show the full address i.e. 9900 to 9908. The inner addresses will only have two i.e. 02, 04, 06, 08. My query will show apartment numbers, but that isn't a concern.
My query:
UPDATE [Parcels].[tbl_Address_Basic]
SET [FONT_TEXT] =
CASE
WHEN [FONT_NUM] = 9 THEN
[APPT_NUM]
WHEN [FONT_NUM] = 4 or [FONT_NUM] = 5 THEN
[ST_NUM]
WHEN [FONT_NUM] = 2 THEN
RIGHT([ST_NUM_TXT], 2)
END
Problem: After update, the column only returns 2,4,6,8 instead of 02,04,06,08. 9900 would return 0. ST_NUM is small int and I thought that was the problem, so I created ST_NUM_TXT which is varchar(10). Same result. I've tried casting and same thing. I tried right('0' + [ST_NUM_TXT], 2) and same thing. I tried ('a' + [ST_NUM_TXT], 2). I know, I know, but I was out of ideas. I received the error: "Conversion failed when converting the varchar value 'a00' to data type smallint". I've tried casting on the inside and outside.
I'm out of ideas. BTW, FONT_TEXT is varchar(10). Any help would be appreciated.
Monte
May 31, 2013 at 8:19 am
What is the data type for [APPT_NUM]?
May 31, 2013 at 8:21 am
Varchar(10). Apartments will not be an issue. There will never be a case where a zero leads the number.
May 31, 2013 at 8:33 am
Noticed you have two columns, [ST_NUM] and [ST_NUM_TXT]. What is the data type for [ST_NUM]?
May 31, 2013 at 8:36 am
Small Int. That why I created ST_NUM_TXT, which is varchar(10)
May 31, 2013 at 8:39 am
mirvin 50540 (5/31/2013)
Small Int. That why I created ST_NUM_TXT, which is varchar(10)
And that is why your values are being converted to smallint's.
Read the following and look closely at the Return Types section:
May 31, 2013 at 8:58 am
Well, kick me in the head. I'm still not sure what is going on here. I took your advice and created a short update:
UPDATE [Parcels].[tbl_Address_Basic]
SET [FONT_TEXT] = RIGHT([ST_NUM_TXT], 2)
WHERE FONT_NUM = 2
The 00,02,04,06,08 did show, so I figured it was because I was using CASE. Then, for giggles I went back to the original and used:
UPDATE [Parcels].[tbl_Address_Basic]
SET [FONT_TEXT] =
CASE
WHEN [FONT_NUM] = 9 THEN
[APPT_NUM]
WHEN [FONT_NUM] = 4 or [FONT_NUM] = 5 THEN
[ST_NUM_TXT]
WHEN [FONT_NUM] = 2 THEN
RIGHT([ST_NUM_TXT], 2)
END
GO
I used the ST_NUM_TXT in the preceding WHEN/THEN and that worked.
I believe if I walk out front and bang my head on the brick wall this may make sense.
Thanks for the help and solution.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply