如何在不创建函数的情况下拆分 SQL Server 中的分隔字符串? [英] How Do I Split a Delimited String in SQL Server Without Creating a Function?

问题描述

我正在使用 SQL Server 数据库.我有一个包含分隔列表的列，我需要编写一个将列表值拆分为行的查询.通过浏览 StackOverflow 和网络的其余部分，我知道这是一个常见问题.事实上，我在这里找到了广泛的分析:

I'm working with a SQL Server database. I have a column which contains a delimited list, and I need to write a query which splits the values of the list into rows. From browsing StackOverflow and the rest of the web, I know this is a common problem. In fact, I found an extensive analysis here:

http://www.sommarskog.se/arrays-in-sql.html

不幸的是，我在该站点和其他地方看到的每个解决方案都需要我创建一个函数.这不是我的选择——我缺乏使用 CREATE 命令所需的权限.

Unfortunately, every solution I've seen on that site and elsewhere requires me to create a function. That isn't an option for me -- I lack the privileges required to use the CREATE command.

如果没有 CREATE，我知道我可以使用 PARSENAME 函数，就像这样(感谢 Nathan Bedford 在 How do I split a string so I可以访问项目 x?.):

Without CREATE, I know I can use the PARSENAME function, something like this (Thanks to Nathan Bedford at How do I split a string so I can access item x?.):

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

但是，PARSENAME 仅适用于 4 个或更少项目的列表.因此，我的问题是:如何编写查询以拆分超过 4 个项目的分隔字符串而不在数据库中创建新对象?

However, PARSENAME works only for lists of 4 items or fewer. My question, therefore, is this: How do I write a query to split a delimited string of more than 4 items without creating new objects in the database?

感谢大家的快速解答.我可能遗漏了一些重要信息——我正在通过 ODBC 连接与数据库交互.除了 CREATE 语句之外，似乎还有其他语句不起作用.例如，我似乎无法在一个语句中使用 DECLARE 来定义将在另一个语句中使用的变量.尽我所能，我必须将所有内容都放入一个 SELECT 语句中(尽管 WITH 似乎也适用于声明公共表).不幸的是，到目前为止建议的所有解决方案似乎都需要在 SELECT 语句之外进行变量声明，而这是行不通的.请耐心等待 - 我正在学习.

Thanks to everyone for the quick answers. I may have left out some important information -- I'm interacting with the database through an ODBC connection. In addition to CREATE statements, there seem to be other statements that don't work. For instance, I can't seem to use DECLARE in one statement to define a variable that will be used in another statement. As near as I can figure, I have to put everything into a single SELECT statement (although WITH also seems to work for declaring common tables). Unfortunately, all of the solutions suggested so far seem to require variable declarations outside the SELECT statement, and that isn't working. Please bear with me -- I'm learning as I go.

推荐答案

使用 XML 的版本.

A version using XML.

declare @S varchar(100) = 'Hello John Smith'

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

使用表格代替将 @T 替换为您正在使用的任何表.

Using a table instead Replace @T with what ever table you are using.

-- Test table
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello John Smith')
insert into @T values (2, 'xxx yyy zzz')

select 
  T.ID,
  n.r.value('.', 'varchar(50)')
from @T as T
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)

不使用变量分割字符串'Hello John Smith'

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace('Hello John Smith', ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

这篇关于如何在不创建函数的情况下拆分 SQL Server 中的分隔字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！