Hi

I'm fairly novice to SQL Server and am trying to make a computed
column in my database

I have the following columns in tblJobs :

JobID
Title
City
State

I'm trying to create a computed column that takes the Title and City
and strips out any spaces and replaces them with a dash, then
concatenates the other columns with dashes. I also have a numeric
ID. So if I have

JobID : 22333
Title : Nurse Assistant
City : Panama City
State : FL

and want the resulting string to look like this : Nurse-Assistant-
Panama-City-FL-22333

I put the following string into the Formula of the Computed Column
Specification:

Replace(tblJobs.Title," ", "-") + "-" + Replace(tblJobs.City," ", "-")
+ "-" + State + "-" + (nvarchar(5), JobID)

yet the Management Studio says it can't validate this.

Am I on the right track here? Or should I try to do the replace (or
typecast) somewhere else?

Thanks for any help you can provide.

Paul

You are on the right track. Where it probably goes wrong is at

"+ (nvarchar(5), JobID)"

You forgot the word CONVERT, as in

"+ CONVERT(nvarchar(5), JobID)"

--
Gert-Jan


jobs (AT) thomassharp (DOT) com wrote:

Thank you so much Gert-Jan. That worked!! I had to actually replaces
the double quotes with single quotes and get rid of the table
declaration for it to work. So, here's what it ended up being :

((((((replace([Title],' ','-')+'-')+replace([City],' ','-'))+'-')+
[State])+'-')+CONVERT([nvarchar](5),[JobID],0))

Thanks again!!! Paul

On Jul 25, 12:19*pm, Gert-Jan Strik
<sorrytoomuchspamalre... (AT) xs4all (DOT) nl> wrote:

(jobs (AT) thomassharp (DOT) com) writes:
In addition to Gert-Jan's comment, observe that the string delimiter in
SQL is ' not ".

Also, you need need to include the table name as a prefix; I don't even
think this is permitted.

The table designer in Management Studio is a crappy tool, and I would not
be surprised if is something it does not support. You are better off using
CREATE TABLE.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx