如何在 SQL Server 中拆分字符串并将值插入表中 [英] How to split string and insert values into table in SQL Server

问题描述

我有一个这样的字符串:

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 中拆分字符串并将值插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！