使用sqlserver 2005在特定位置插入值

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

如何使用sql查询插入“color”?我使用sql server 2005,c#。谢谢你！

4 个解决方案

#1

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).

如果这些都是正确的，下面的内容可能会对您有所帮助。创建两行测试数据——然后用新名称(颜色)和值(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

#2

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();

#3

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.

我将该列转换为XML，并将数据作为键-值对存储在数据库中。然后，您可以向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.

如果需要向后/遗留兼容性，还可以创建该表的视图，将XML转换回上面的格式。

#4

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).

使用自定义CLR函数或SQL#首先使用SQL# split函数将字符串分割为一个列表(使用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.

使用结果数据集上的自定义聚合“join”函数以逗号分隔的列表形式重新组装。

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

现在可以使用UPDATE语句将数据写回。

#1