如何将逗号分隔的数字字符串解析为临时 orderId 表? [英] How to Parse a comma delimited string of numbers into a temporary orderId table?
问题描述
我有一堆 orderIds '1, 18, 1000, 77 ...',我正在从 nvarchar(8000) 中检索它们.我正在尝试解析这个字符串并将 id 放入一个临时表中.有没有简单有效的方法来做到这一点?
要查看我解析的所有 orderId 的列表,我应该能够这样做:
I have a bunch of orderIds '1, 18, 1000, 77 ...' that I'm retreiving from a nvarchar(8000). I am trying to parse this string and put the id into a temporary table. Is there a simple and effective way to do this?
To view a list of all the orderIds that I parsed I should be able to do this:
select orderid from #temp
推荐答案
通过需要几秒钟创建的数字表的最快方法:
The fastest way via a numbers table that takes seconds to create:
--First build your numbers table via cross joins
select top 8000 ID=IDENTITY(int,1,1)
into numbers
from syscolumns s1
,syscolumns s2
,syscolumns s3
GO
--add PK
alter table numbers add constraint PK_numbers primary key clustered(ID);
GO
create table #temp(
ID int identity(1,1) primary key,
StringValues varchar(8000)
)
declare @orderIds varchar(8000)
set @orderIds = ',1, 18, 1000, 77, 99, 1000, 2, 4,'
insert into #temp(StringValues)
select substring(@orderIds,ID+1,charindex(',',@orderIds,ID+1)-ID-1)
from numbers where ID < len(@orderIds)and substring(@orderIds,ID,1) = ',';
根据以下文章,这是我多年来一直使用的一种很好的方法:http://www.sqlservercentral.com/articles/T-SQL/62867/一个>
This is a great method I've been using for years based on the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/
这篇关于如何将逗号分隔的数字字符串解析为临时 orderId 表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!