如何在sqlserver 2008中拆分值 [英] How to split the values in sqlserver 2008

问题描述

船只名称: PAC BINTAN，
航程号: 01，
IGM编号: C-95/12，
股东特别大会编号: C-108/12，
航程类型-沿海/国外: COASTAL RUN，
呼叫端口: VISAKHAPATNAM，
客户名称:立顿承包商(印度)有限公司

我从上面的表中选择数据.然后，如何在sqlserver 2008中拆分上面的下划线值并以表形式(如下面)返回.

结果:

PAC民丹岛
01
C-95/12
C-108/12
沿海跑步
VISAKHAPATNAM
莱顿承包商(印度)有限公司


如果有人知道这个告诉我.


问候
Nanda Kishore.CH

Hi,

Vessl Name:PAC BINTAN,
Voyage No:01,
IGM No.:C-95/12,
EGM No.:C-108/12,
Type of Voyage - Coastal/foreign:COASTAL RUN,
Port of Call :VISAKHAPATNAM,
Clients Name:LEIGHTON CONTRACTORS(INDIA)LTD

I select data from the table as above.Then, How to split the above underline values and return in a table(Like below) form in sqlserver 2008.


Result:

PAC BINTAN
01
C-95/12
C-108/12
COASTAL RUN
VISAKHAPATNAM
LEIGHTON CONTRACTORS(INDIA)LTD


if any one know about this tell me.


Regards
Nanda Kishore.CH

推荐答案

Function
	@listString VARCHAR(8000),
	@Delimeter char(1)


RETURNS @ValueTable table 
(			
	Value VARCHAR(8000)
) 

AS
BEGIN

	DECLARE @NextString VARCHAR(8000)
	DECLARE @Pos INT
	DECLARE @NextPos INT
	DECLARE @String VARCHAR(8000)
	DECLARE @Delim VARCHAR(1)

	SET @NextString = ''
	SET @String = @listString
	SET @Delim = @Delimeter
	SET @String = @String + @Delim
	SET @Pos = charindex(@Delim,@String)

	WHILE (@pos <> 0)
		BEGIN
			SET @NextString = substring(@String,1,@Pos - 1)	
			INSERT INTO @ValueTable (Value) Values (@NextString)		
			SET @String = substring(@String,@pos+1,len(@String))
			SET @pos = charindex(@Delim,@String)			
		END 
		
	RETURN

END
GO

将分界符值传递为:".

pass delimeter value as '':''.

阅读以下内容:
http://stackoverflow.com/questions/2507330/sql-server-split-operation [ ^ ]

http://stackoverflow.com/questions/1869465/indexof-function-in-t-sql [ ^ ]

Read the following :
http://stackoverflow.com/questions/2507330/sql-server-split-operation[^]

http://stackoverflow.com/questions/1869465/indexof-function-in-t-sql[^]

您好，Nanda Kishore，

试试这个代码块

Hi Nanda Kishore,

Try this code block

WITH SplitCTE AS (
SELECT CAST('<i>' + REPLACE(DelimitedColumn, ',','</i><i>') + '</i>' AS     XML) AS Strings
FROM YourTableName WHERE <CONDITION>
)

-- Xquery to get the desired result set using CROSS JOIN
SELECT  SUBSTRING(x.i.value('.', 'VARCHAR(MAX)'),CHARINDEX(':',x.i.value('.',                                                 'VARCHAR(MAX)'))+1,LEN(x.i.value('.', 'VARCHAR(MAX)'))) AS Strings
FROM SplitCTE CROSS APPLY Strings.nodes('//i') x(i)

这篇关于如何在sqlserver 2008中拆分值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！