Computed column 1

[sparkbyte]

I would like to add a computed column to a table.

Alter Table Mailroom.tblTrackingTable
Add 
	[TrackingNumberPrefix]  AS (upper(substring([BoxNumber],(1),(2)))) Where (upper(substring([BoxNumber] = '1Z')

Error with where, I also tried it as a Select and get a message saying that sub-query not allowed in this context.



Thanks

John Fuhrman

 

[markros]

You can not use WHERE clause in the computed column specification. What do you want to do if the BoxNumber does not start with '1Z'?

I suggest to try just
upper(substring(BoxNumber,1,2))

although this computed column looks a bit strange and I don't see why would you need to create such computed column.



PluralSight Learning Library

 

[gmmastros]

You can use a case statement, like this...

Alter Table Mailroom.tblTrackingTable
Add 
    [TrackingNumberPrefix]  AS (Case When BoxNumber Like '1Z%' Then Upper(Left(BoxNumber, 2)) Else '' End)

If the BoxNumber does not start with 1Z, then the TrackingNumberPrefix will be an empty string (with the code I show above).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

The issue is that not all tracking numbers start with a 2 digit prefix, Only the UPS tracking numbers start with the 1Z prefix.

We tipically have tracking numbers like.

1Z1A1234B0187654321 --> This is a typical format for UPS tracking Number
NBC20112227151-705 --> Tranfser to another facility


Something thing this.

Select Top 100
		BoxNumber,
		Case
			When UPPER(Left(BoxNumber,2)) = '1Z' 
			Then UPPER(Left(BoxNumber,2))
			Else Left(BoxNumber, PatIndex('%[^a-z.]%', BoxNumber + '1')-1) 
		End As BoxNumberPrefix
From Mailroom.tblTrackingTable
Order By Tracking_ID Desc

Output
[tt]
BoxNumber BoxNumberPrefix
--------------------------------------------- ---------------------------------------------
NBC201122293523-401 NBC
NBC201122293523-401 NBC
1Z1A123B0187654321 1Z
NBC201122293523-401 NBC
NBC201122293523-401 NBC
1Z1A123B0187654321 1Z
NBC201122293523-401 NBC
1Z1A123B0187654321 1Z
[/tt]



Thanks

John Fuhrman

 

[markros]

I see, just use this expression as is in the computed column declaration.

PluralSight Learning Library

 

[sparkbyte]

How do you mean?

Thanks

John Fuhrman

 

[markros]

Alter table MyTable
ADD BoxNumberPrefix varchar(10) as
Case
            When UPPER(Left(BoxNumber,2)) = '1Z' 
            Then UPPER(Left(BoxNumber,2))
            Else Left(BoxNumber, PatIndex('%[^a-z.]%', BoxNumber + '1')-1) 
        End

Something along these lines.

PluralSight Learning Library

 

[sparkbyte]

Thanks That did it.

Here is the final

Splits apart the whole UPS Tracking Number.

Alter table Mailroom.tblTrackingTable
ADD TrackingNumberPrefix As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then UPPER(Left(BoxNumber,2))
            Else Left(BoxNumber, PatIndex('%[^a-z.]%', BoxNumber + '1')-1) 
        End 
Go
Alter table Mailroom.tblTrackingTable
ADD TrackingNumberAct As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then upper(substring(BoxNumber, 3, 6))
        End 
Go
Alter table Mailroom.tblTrackingTable
ADD TrackingNumberShipping As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then upper(substring(BoxNumber, 9, 2))
        End 
Go
Alter table Mailroom.tblTrackingTable
ADD TrackingNumberParsel As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then upper(substring(BoxNumber, 11, 8)) 
        End 
Go

Thanks

John Fuhrman

 

[sparkbyte]

Made a slight change to it to set the data types properly.

Alter table Testing.tblTrackingTable
ADD TrackingNumberPrefix As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then Cast(UPPER(Left(BoxNumber,2)) As VarChar(2))
            Else Cast(Left(BoxNumber, PatIndex('%[^a-z.]%', BoxNumber + '1')-1) As VarChar(3))
        End 
Go
Alter table Testing.tblTrackingTable
ADD TrackingNumberAct As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then upper(substring(BoxNumber, 3, 6))
        End 
Go
Alter table Testing.tblTrackingTable
ADD TrackingNumberShipping As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then Cast(upper(substring(BoxNumber, 9, 2)) As Int)
        End 
Go
Alter table Testing.tblTrackingTable
ADD TrackingNumberParsel As
		Case
            When UPPER(Left(BoxNumber,2)) = '1Z' Then Cast(upper(substring(BoxNumber, 11, 8)) As Int)
        End 
Go

Because these are computed columns you cannot specify the data type as per.

[tt]
Alter table MyTable
ADD BoxNumberPrefix varchar(10) as
[/tt]

But SQL Server does set the type correctly if you CAST them.

Hope this little tidbit helps someone else.


Thanks

John Fuhrman