如何在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 '':''.

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