February 22, 2012 at 1:03 pm
Hi all I'm a bit of a newbi so bare with me.
Using sql2008
I've put this into a computed column
(([prefix]+'-')+right('0000'+convert([varchar],[numeric],(0)),(4)))
is persisted =yes
This works but I need it to give a not null result so I can attach a primery key.
Any help would be brill
Cheers
grk
February 22, 2012 at 1:39 pm
If I understand correctly:
((COALESCE([prefix]+'-'), '')+right('0000'+COALESCE(convert([varchar],[numeric],(0)), ''),(4)))
is persisted =yes
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 22, 2012 at 1:43 pm
May be this ?
IF OBJECT_ID('CompuCol','U') IS NOT NULL
DROP TABLE dbo.CompuCol
CREATE TABLE dbo.CompuCol
(
ID INT IDENTITY(1,1)
,FName VARCHAR(20)
,LName VARCHAR(20)
,FullName AS (ISNULL(FName,'') + ' ' + ISNULL(LName,'')) PERSISTED
,CONSTRAINT [PK_CompuCol(FullName)] PRIMARY KEY CLUSTERED (FullName)
)
INSERT INTO dbo.CompuCol (FName,LName)
SELECT 'A' , 'Mr'
UNION ALL SELECT 'B',''
UNION ALL SELECT '','Jr'
UNION ALL SELECT NULL,'D'
UNION ALL SELECT 'E',NULL
UNION ALL SELECT NULL , NULL
U got to wrap the columns used in computed column with ISNULL ( or COALEASE to have better control over the data-type precedence).
February 22, 2012 at 1:44 pm
Ouch, Scott beat me to it. 🙂
February 22, 2012 at 1:53 pm
excellent people, sorted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply