Inserting value at a specific position using sqlserver 2005

Question

hello.. i have a database table which contains some fields. one of the field's value is as like

,code,removeformat,undo,redo,cut,copy,color,|1|1,2,3,|0|500,400|1078,|False|500|

how can i insert "color" as shown using sql query this ? i using sql server 2005,c#. thank you

Answer 1

I would convert that column to XML and store your data as a key-value pair in the database. Then you can just add the extra values to the XML.

You can also create a View of this table to convert the XML back into the format you have above if it is needed for backwards / legacy compatibility.

Answer 2

It's very difficult to provide useful assistance without more information about the schema of the table you want to update, and some before and after data sets.

I've assumed that the column holds a set of name/value pairs to which you want to add a new pair for known row(s), and that the order of the pairs isn't important (i.e. it's acceptable to always add new values at the end of the list).

If all of this is correct, the following may help you. Two rows of test data are created - the first row is then updated with a new name (color) and value (500).

DECLARE @t TABLE
(id INT
,attributes VARCHAR(MAX)
)

INSERT @t
       SELECT 1,',code,removeformat,undo,redo,cut,copy,|1|1,2,3,|0|500,400|1078,|False|'
UNION  SELECT 2,',code,removeformat,undo,redo,cut,copy,|1|1,2,3,|0|500,400|1078,|False|'


UPDATE @t
SET attributes = LEFT(attributes,CHARINDEX('|',attributes,0) - 1 ) + 'color,' + SUBSTRING(attributes,CHARINDEX('|',attributes,0),999999) + '500|'
WHERE id = 1

SELECT *
FROM @t

Answer 3

NOTE: This assumes order doesn't matter within the string.

Use either a custom CLR function or SQL# to first split the string in to a 1 column table using the SQL# Split function (use CROSS APPLY).

Then UNION on the value you wish to insert on the back of the query you wrote to split the string.

Use a custom aggregate 'join' function on the resulting dataset to re-assemble as a comma separated list.

You can now use an UPDATE statement to write the data back.

Answer 4

just found a solution. i don't know if it is good one.. you can give me normalized solution..

int startIndex = test.LastIndexOf(",|1");  
string insert = test.Insert(startIndex, ",color");  
SqlCommand cmd = new SqlCommand("update tableName set value='" + insert + "' where Id='1'", con);  
cmd.ExecuteNonQuery();