Adding data to a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding data to a string

Author

Topic

romanorus
Starting Member

3 Posts

Posted - 2007-09-13 : 12:43:41

Hi,

I'm not a programmer by trade, so please be gentle with me! I am trying to add or insert data to the beginning of an existing string of data. The string is in one field in a table, with various options seperated by [ ].
An example of the current data is:
[Eye Color:TEST][Hair Color:TEST]
and I would like to add the data [# Persons:1] to the beginning of the string so it looks like this:
[# Persons:1][Eye Color:TEST][Hair Color:TEST]

Can anyone help me with the best way to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-13 : 12:49:56

This will give you the gist of what you need. The selects are there just so you can see the string get built. You'd have to give us some sample data from the various tables, with expected output, to write you a query

Jim

DECLARE @str varchar(100)
SET @str = 'Hair Color:TEST'
select @str
SET @str = 'Eye Color:TEST' + @str
select @str
SET @str = 'Persons:1' + @str
select @str

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 12:50:25

update table1
set col1 = '[# Persons:1]' + Col1
where id = 2

E 12°55'05.25"
N 56°04'39.16"

romanorus
Starting Member

3 Posts

Posted - 2007-09-13 : 15:25:17

Thanks for the quick responses! I tried Peso's solution, with this command:
UPDATE OrderDetails
SET Options = '[# Persons:1]' + Options
WHERE OrderDetailID = 4
And I got this error message:
Invalid operator for data type. Operator equals add, type equals ntext.
Part of the issue is also that the other data in the column will vary, and can be quite long. I'd like to be able to insert the [# Persons:1] data into the beginning of the column, regardless of what else is already in the column. Is this even possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:28:06

Are you using SQL Server 2005? If so, change the datatype to VARCHAR(MAX)!

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:28:24

Wouldn't you rather normalize he data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

romanorus
Starting Member

3 Posts

Posted - 2007-09-13 : 15:39:19

We are using SQL Server 2000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources