如何在sql server 2008中将一大量数据列拆分为两列或三列？

软件工程师Hello,I have a four column for one address in my table and every each column size is 50 character length, so i want to split data after 50 character int second or third column.like example:-Address140/3 abcd, narayana, delpin code-201101i want to split this address after 50 character.How can be possible in sql server 2008?Please help me.ThanksAnkit AgarwalSoftware Engineer推荐答案我最喜欢的方法是使用常用表格表达式 [ ^ ]。 示例：My favorite method is to use Common Table Expressions[^].Example:DECLARE @fullAddress NVARCHAR(255)SET @fullAddress = '140/3 abcd, narayana, delhi,pin code-201101';With AddressParts AS( SELECT 1 AS PartNumber, LTRIM(LEFT(@fullAddress, CHARINDEX(',',@fullAddress)-1)) AS Part, RIGHT(@fullAddress, LEN(@fullAddress) - CHARINDEX(',',@fullAddress)) AS Remainder UNION ALL SELECT PartNumber + 1 AS PartNumber, LTRIM(LEFT(Remainder, CHARINDEX(',',Remainder)-1)) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder FROM AddressParts WHERE CHARINDEX(',', Remainder)>0 UNION ALL SELECT PartNumber + 1 AS PartNumber, LTRIM(Remainder) AS Part, NULL AS Remainder FROM AddressParts WHERE CHARINDEX(',', Remainder)=0)SELECT *FROM AddressParts 以上查询返回：Above query returns:PartNo. Part Remainder1140/3 abcd narayana, delhi,pin code-2011012narayana delhi,pin code-2011013delhi pin code-2011014pin code-201101 NULL怎么样How aboutselect left(FIELDNAME, 50) as AddressPart1 from TABLENAME 使用udf将地址拆分为3列 [ ^ ] SQL SERVER - 从电子邮件地址中选择域名 [ ^ ] 在多列中拆分一列 [ ^ ] 希望上面的链接会给你一些想法Split Address into 3 columns using a udf[^]SQL SERVER – Selecting Domain from Email Address[^]splitting one column in multiple columns[^]Hope above links will give u some idea 这篇关于如何在sql server 2008中将一大量数据列拆分为两列或三列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！