sub string 1

[angela4eva]

I am using sql server 7
i have afield called input in the table getinfo,
the "input field has data like 112e|wee|rer3
112e|wee|rer3
wede|weee|null
null|null|null
NULL
null|wesa|dssew

I want a help with writing a query
all the first parts second parts and thrid parts
for eg:first part should return
112e
112e
wede
null
null
i hope you understand what i mean i should be able to do this for all the three parts...
as you can see"|" is the delimiter

any help appreciated
any help apprecviated

 

[sshelper]

You can use PARSENAME to get the different parts of your string. Here's a couple of links that discusses a similar way in using the PARSENAME:

http://www.sql-server-helper.com/tips/split-name.aspx

http://www.sql-server-helper.com/tips/sort-ip-address.aspx

SQL Server Helper

http://www.sql-server-helper.com

 

[DotNetGnat]

here you go:

create this UDF in your database:

CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(6)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

then you call this function as follows:

SELECT Data FROM dbo.split(yourfield,'|')

-DNG