问题描述
我有一个这样的字符串:
I have a string like this:
72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX
这基本上是 3 行(来自 ASP.NET 网格)中的每行 5 个值.我需要将此字符串拆分为 SQL Server 表中的 5 列和 3 行.各个值以逗号分隔，行以冒号分隔.
This is basically 5 values in each of 3 rows (from an ASP.NET grid). I need to split this string apart into 5 columns and 3 rows in a SQL Server table. Individual values are separated by commas and rows by colons.
我找到了一个函数来将一个字符串分割成几部分，我可以从这个字符串中取出行:
I found a function to split a string into pieces and I can get the rows out of this string:
declare @testString varchar(100)
set @testString = '72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX'
select *
from dbo.SplitString(@testString, ':')
给我:
72594206916,2,1/2/08,Tacoma,WA
72594221856,5,5/7/13,San Francisco,CA
72594221871,99,12/30/12,Dallas,TX
这给了我一个包含三行的结果集(该函数输出一个表格).我可以同时再次调用此函数并将其输出以某种方式插入表中吗?
This gives me a result set with the three rows (the function outputs a table). Can I call this function again at the same time and insert its output into a table somehow?
推荐答案
假设您的拆分返回列名称 item
assuming your split returns column name item
insert <table> (colname)
select y.item
from dbo.SplitString(@testString, ':') x
cross apply
dbo.SplitString(x.item, ',') y
这篇关于如何在 SQL Server 中拆分字符串并将值插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！