如何将逗号分隔的字符串添加为列值 [英] How to add comma seperated strings as column value

问题描述

在数据库中，有一个包含三列的表，第一列是自动生成的id字段，第二列是资源，第三是'课程'。 '资源'可以有很多'类'。例如，假设一行的资源字段值为房间。同一行中的课程价值将是卧室，餐厅，厨房等..我想插入卧室，餐厅，厨房，在课程栏中以逗号分隔。我怎么能为此写一个插入查询？

解决方案

基本上，不要 - 这很容易做到，但是无论什么时候你都有一个完全的噩梦做任何改变。 SQL字符串处理非常糟糕，你必须真正搞乱才能做任何事情。

相反，创建一个第二个表，它将一个Class值链接到一个资源ID，并在其中有多个条目。

在实践中，我要做的是有三个表：

资源：

 Id 
 ... 

课程：

 Id 
房间

ClassesInResource

 Id 
 ResourceId 
 ClassId 

并填写：

资源：

 1 John Smith 
 2 Mary Jones 

课程：

<前lang =文字> 1卧室
2厨房
3餐厅
4休息室

ClassesInResource：

 1 1 1 
 2 1 2 
 3 1 3 
 4 2 1 
 5 2 3 
 6 2 4 

它可能看起来更复杂 - 它是 - 但它使用起来非常简单，它使后来的编码变得更加容易，更容易！

创建一个用户定义的函数，将逗号分隔值转换为表格。用户定义的函数，它接受逗号分隔的值并返回包含这些值的表。

查看此帖子

sql-server-comma-separated-string-to-table.aspx [ ^ ]

Hi,

In the database , there is a table with three columns, First column is an autogenerated id field, second one is named as 'Resources' and third is 'Classes'. the 'Resources' can have many 'Classes'. For example suppose the 'Resource' field value of one row is 'Room'. The 'Classes' Value in the same row will be bed room, dining room, kitchen..etc.. I would like to insert bed room, dining room, kitchen, in the 'Classes' column with a comma seperation. How can i write an insert query for this?

解决方案

Basically, don't - it's very easy to do, but it's a total nightmare to work with whenever you have to make any changes. SQL string handling is pretty poor and you have to really mess about in order to do anything.
Instead, create a second table which links a Class value to a Resource ID, and have multiple entries in that.
In practice, what I would do is have three tables:
Resources:

Id
...

Classes:

Id
Room

ClassesInResource

Id
ResourceId
ClassId

And fill them:
Resources:

1      John     Smith
2      Mary     Jones

Classes:

1      Bedroom
2      Kitchen
3      Dining room
4      Lounge

ClassesInResource:

1      1      1
2      1      2
3      1      3
4      2      1
5      2      3
6      2      4

It may seem more complicated - and it is - but it's really very simple to use, and it makes later coding a lot, lot easier!

create a user-defined function that will convert the comma separated value into a table. user-defined function that takes a comma-delimited value and returns a table containing these values.
See this post
sql-server-comma-separated-string-to-table.aspx[^]

这篇关于如何将逗号分隔的字符串添加为列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！