Appending a Field

I know this must be simple, but I am stumpted, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?This one should be quite easy:

UPDATE table SET Filed = Field + @.Value WHERE ID = @.ID|||Thank you very much!

It is working now.

Appending a Field

I know this must be simple, but I am stumpped, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?update tablename set fieldname = fieldname + @.incomingtext where condition ...|||Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.|||Originally posted by Donner
Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.

create table test(id int identity,code varchar(50))
go
insert test(code) values('a')
go
update test set code=code+'b' where datalength(code+'b')<51
go
select * from test

append single character at specific position in string

Hi everyone, i am in s of help.
I have a variable (called status) of type char(10). This needs to be
extended to type char(11) and i need to append the character 'N' at this
position. For example, if 'status' is any of the following strings of type
char(10)
NYNYYN NY
NNYYY
Y
NNNY YYYNN
These must be converted to the following strings of type char(11) ..<note
the 'N' at the end of the string>:
NYNYYN NYN
NNYYY N
Y N
NNNY YYYNNN
Would anyone know how this can be done?
Any help most appreciated.
Kindest regards,
Sarah.Based on you narrative, you seems you can simply concatenate. For example:
DECLARE @.status char(10)
SELECT @.status = 'NYNYYN NY'
SELECT @.status + 'N'
Hope this helps.
Dan Guzman
SQL Server MVP
"sarah.clarke" <s.clarke@.nospam.com> wrote in message
news:eSgTNXqHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>|||First off, alter the table and change the column to char(11). That's the
easy part.
update MyTable
set status = left(isnull(status,'') + ' ', 10) + 'N'
Note: There are 10 spaces between the quotes.
What this statement does is to convert any null values to an empty string.
Then, it concatenates 10 spaces.
Then, it takes the left most 10 characters.
Finally, it concatenates the 'N' as the eleventh character.
If you want null values to remain null, simply remove the isnull function.
All the concatenation will return null and the row will still have a null
value in the status column.
Hope that helps,
Joe
"sarah.clarke" wrote:

> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>
>|||oops...my previous post was regarding a column
using a variable:
declare @.status char(11)
set @.status = 'A'
set @.status = left(@.status, 10) + 'N'
Hope that helps,
Joe

Append Counter variable to field name

Hi all,

I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!

Here's the proc:

WHILE @.Counter < 4
BEGIN

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @.ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @.DateFrom) AND (tbl_SurgerySlot.StartTime < @.DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)

SET @.FieldName = 'Days' + CONVERT(VARCHAR(20),@.Counter)

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

SET @.DateTo = DATEADD(Day,1,@.DateTo)

--Increment the loop counter
SET @.Counter = @.Counter + 1

When I run the above the follwoing message is displayed:

Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.

The object IS valid so I'm lost...Try to use object owner (object_owner.table_name)|||Instead of :

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

you could use exec:

exec('insert into ...'+ @.FieldName+') ...'|||OK, thanks, I'll give that a go...|||You can turn the whole thing in to a set based solution..

Also, are you sure that the query will return 1 row...|||Hi Bret,

Not sure what u mean by a Set based solution. I've just realised that the way I'm doing this won't work anyway cos' every time I use the INSERT statement it will obviously insert a new row, which I don't want it to do. I want the code to fill up the row with firgures for each day e.g.

Row1 5, 25,6

At the mo' it will do:

Row1 5,
Row2 ,25,
Row3 , , 6

Thought I could maybe store the data in an Array before committing it to the DB but have found T-SQL doesn't support this! Any ideas...|||The are no arrays in sql server...

I guess you could call a table like an array...

If you have sql server 2000 you can use table varialbles...

And I'm kinda of lost (so what else in new) with your example

Can you tell us, in business terms, what you're trying to do?|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)

Thanks for the chuckle...

Lots of time sql server will through an erroneous error...

BUT...your process needs to be changed...

If you ever figure out what's suppose to happen, tell use and post the ddl of the tables, some sample data with dml statements and expected results..

good luck...

Apostrophes in field

If I am using a variable to retrieve a field value and it has an apostrophe in the field, I am getting an error

DECLARE @.C_CenterName nvarchar(255)

...

FETCH NEXT FROM curJCD INTO @.C_CenterName,@.Address,@.City,@.State,@.Zip

...

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + @.C_CenterName + ',' + @.Address + ',' + @.City + ',' + @.State + ',' + @.Zip + ',1,1);'

@.C_CenterName sometimes has apostrophe

ERROR BELOW:

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark before the character string

How do I get around that if the fields that have the apostrophe are random?

Jason

Hi Jason

You can use the QUOTENAME function, like so:

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + QUOTENAME(@.C_CenterName, '''') + ',' + QUOTENAME(@.Address, '''') + ',' + QUOTENAME(@.City, '''') + ',' + QUOTENAME(@.State, '''') + ',' + QUOTENAME(@.Zip, '''') + ',1,1);'

This example assumes that your variables are not already delimited with single quotes. I should have spotted this when you posted your previous question.

Again, here's a link that might help:

http://msdn2.microsoft.com/en-us/library/ms176114.aspx

Chris

|||

Just taking a step back for a moment, I'm not sure why you're using dynamic SQL at all in this code.

You could, in fact, should, just use:

INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers])

VALUES(@.NextClientID, @.C_CenterName, @.Address, @.City, @.State, @.Zip,1,1)

Chris

|||

THX. I think i got side tracked by my other problem but this worked great.

Jason