如何在sql server 2008中将一大量数据列拆分为两列或三列？ [英] How to split one large amount of data column into two or three columns in sql server 2008?

问题描述

您好，



我的表格中有一个四列用于一个地址，每个列的大小都是50个字符长度，所以我希望在50之后分割数据字符int第二列或第三列。

例如： -

地址

140/3 abcd，narayana，delhi，pin code-201101



i希望在50个字符后拆分此地址。



如何在sql server 2008中实现？



请帮帮我。



谢谢

Ankit Agarwal
软件工程师

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:-
Address
140/3 abcd, narayana, delpin code-201101

i want to split this address after 50 character.

How can be possible in sql server 2008?

Please help me.

Thanks
Ankit Agarwal
Software 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             Remainder
1	140/3 abcd	 narayana, delhi,pin code-201101
2	narayana	 delhi,pin code-201101
3	delhi	         pin code-201101
4	pin code-201101	 NULL

怎么样

How about

select 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中将一大量数据列拆分为两列或三列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！