<div>
usage: 
 
select * from SomeTable 
cross apply [dbo].[fn_DelimitedToTable](IPAddress,N'|') 

<pre><code class="code-10 nolinks" id="code-259466">if object_id('[dbo].[fn_DelimitedToTable]') is not null
 drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
 (ident int not null identity primary key clustered
 ,thePosition int not null
 ,theValue nvarchar(max)
 )
as
/************************************************************
*
* Author: Brandon Galderisi
* Last modified: 07-Oct-2008
* Purpose: splits an input string (@DelimitedString) 
* on a delimiter (@delimiter) and outputs 
* a table of values.
* 
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n &lt;= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table. This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function. Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with cte0 as (select 1 as c union all select 1), 
 cte1 as (select 1 as c from cte0 a, cte0 b), 
 cte2 as (select 1 as c from cte1 a, cte1 b), 
 cte3 as (select 1 as c from cte2 a, cte2 b), 
 cte4 as (select 1 as c from cte3 a, cte3 b), 
 cte5 as (select 1 as c from cte4 a, cte4 b), 
 nums as (select row_number() over (order by c) as n from cte5)
 select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO
</code></pre>
</div>

<div>
Awesome thanks to you both, worked like a charm!!!
</div>

<div>
<div class="content wysiwyg-content">
I have a column in a table that has multiple values seperated by &quot;|&quot;. &nbsp;I want to create rows for each value. 
Each entry in the coumn can have 1 or 100 and I need to have each as a seperate row. 
 
Here is an example of the data: 
 
serverid &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IpAddresses 
101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;172.25.230.119 
102 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.42.64.235|10.42.64.73|10.42.64.239|10.42.64.238 
103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.99.90.21|10.99.90.103|10.99.90.102 
 
I need the data to return as: 
 
101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 172.25.230.119 
102 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.42.64.235 
102 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.42.64.73 
102 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.42.64.239 
102 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.42.64.238 
103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.99.90.21 
103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.99.90.103 
103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.99.90.102 
 
 
&nbsp; 

</div>
</div>

I have a column in a table that has multiple values seperated by "|".  I want to create rows for each value.
Each entry in the coumn can have 1 or 100 and I need to have each as a seperate row.

Here is an example of the data:

serverid                   IpAddresses
101                          172.25.230.119
102                          10.42.64.235|10.42.64.73|10.42.64.239|10.42.64.238
103                          10.99.90.21|10.99.90.103|10.99.90.102

I need the data to return as: 

101                         172.25.230.119
102                         10.42.64.235
102                         10.42.64.73
102                         10.42.64.239
102                         10.42.64.238
103                         10.99.90.21
103                         10.99.90.103
103                         10.99.90.102


  


&quot;|&quot; Delimited column to rows

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

&quot;|&quot; Delimited column to rows

"|" Delimited column to rows