Concatenation in a Case Statement 1

[chromarog]

I either need a resolution to what I have or a cleaner way to get the desired results and I've looked around, I keep getting a "EUREKA" moment only to be disappointed that the results are still the same.

I'm trying to get a 3 digit number from a longer string. It's a varchar column. The last three digits are key to the uniqueness of the record.

select max(right(CardNumber,3)) as cardprefix
from tblVSKB_CardData
where partnumber = 'N0090026'
group by PartNumber

This returns a '002', I need to increment that number but when I do...

select max(right(CardNumber,3))+1 as cardprefix
from tblVSKB_CardData
where partnumber = 'N0090026'
group by PartNumber

I get '3'. I need it to be '003'.

So I tried taking the results and casing it with the LEN function. (This probably was a terribly bad idea in trying to keep it clean and that's where I would appreciate some help in that regard.)

select
case when len(max(right(CardNumber,3))+1) = 1
then '00'+(
select max(right(CardNumber,3))
)
end
as prefix
from tblVSKB_CardData
where partnumber = 'N0090026'
group by PartNumber

The beauty here is that the result was '00002' That was a "EUREAK!!" moment. However, when I add that fateful +1 to the mess, I'm still getting one digit.

select
case when len(max(right(CardNumber,3))+1) = 1
then '00'+(
select max(right(CardNumber,3))+1
)
end
as prefix
from tblVSKB_CardData
where partnumber = 'N0090026'
group by PartNumber

The result is still '3'. I know it's dropping the concatenation because when the prefix is a returned value like 122 and the case is adjusted to ...len(max(right(CardNumber,3))+1) = 3..., I still just get 123. But when the THEN clause has the +1 removed, it returns a '00122'. Keep in mind this won't be scenario I just used it as a test.

Please help. I'm way behind on getting this thing off the ground.

Thanks,
Rog...

 

[gmmastros]

select  [!]Right('000' + Convert(VarChar(3),[/!] max(right(CardNumber,3))+1[!]), 3)[/!] as cardprefix
from    tblVSKB_CardData
where   partnumber = 'N0090026'
group by PartNumber

The problem you are having is caused by SQL Server auto-magically converting your data types. You see, '002' is a string. To add 1, you need it to be a number, right? SQL Server does that for you. Once it is a number, SQL Server will not auto-magically convert it back to a string. You need to do that manually using the convert function.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here's some test code to highlight the point.

[green]-- Set up test data[/green]
Declare @CardNumber VarChar(20)
Set @CardNumber = 'xyz002'

[green]-- Returns a string[/green]
Select Right(@CardNumber, 3)

[green]--Still returns a string (max based on string sorting)[/green]
Select Max(Right(@CardNumber, 3))

[green]-- Auto-magically converted to number so we can add 1[/green]
Select Max(Right(@CardNumber, 3)) + 1

[green]-- Need to manually convert back to a string[/green]
Select Convert(VarChar(3), Max(Right(@CardNumber, 3)) + 1)

[green]-- Add 3 zero's to the left.  This can result in a 4, 5, or 6 length string[/green]
Select '000' + Convert(VarChar(3), Max(Right(@CardNumber, 3)) + 1)

[green]-- take the 3 right characters of the result.[/green]
Select Right('000' + Convert(VarChar(3), Max(Right(@CardNumber, 3)) + 1), 3)

I encourage you to copy/paste this code to a query windows and run it. This code should illustrate the point quite well, but if you have any questions, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chromarog]

The first block gave me exactly what I wanted and I appreciate it greatly. Your second post was GOLD. It broke it all down for me. I owe you a beer or a beverage of your choice.

Thanks a million.
Rog...

 

[gmmastros]

beverage of your choice

Unsweetened iced tea with lemon.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom