Prev: ColumnIndexOutOfRangeException
Next: Logical proof of SQL change - HELP please!
From: jobs on 25 Jul 2010 11:48
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
From: Gert-Jan Strik on 25 Jul 2010 12:19
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(a)thomassharp.com wrote:
>
> 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
From: ptvvee on 25 Jul 2010 13:15
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...(a)xs4all.nl> wrote:
> 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
>
> j...(a)thomassharp.com wrote:
>
> > 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

From: Erland Sommarskog on 25 Jul 2010 13:18
(jobs(a)thomassharp.com) writes:
> I put the following string into the Formula of the Computed Column
> Specification:
>
> Replace(tblJobs.Title," ", "-") + "-" + Replace(tblJobs.City," ", "-")
> + "-" + State + "-" + (nvarchar(5), JobID)

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.

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

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: ColumnIndexOutOfRangeException
Next: Logical proof of SQL change - HELP please!