declare @vcString as varchar(500)
declare @xmlVar as xml
SET @vcString ='0101250|0121250|0211250|0311250|0411250|0431250|0511250'

SET @vcString = replace(@vcString,'|','</Row><Row>')
--print @vcString

SET @vcString='<Rows><Row>' + @vcString + '</Row></Rows>'

SET @xmlVar=CAST(@vcString as XML)
--print @vcString

SELECT
	t.u.value('.','varchar(50)')
FROM	
	@xmlVar.nodes('Rows/Row')t(u)

WITH N1(N) AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(N))
,N2(N) AS (SELECT 1 FROM N1 a, N1 b)
,N4(N) AS (SELECT 1 FROM N2 a, N2 b) 
,N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N4)
--select * from N
,DestSplit1
AS
(
	SELECT <tbl_MitsolRatePK>
		,SUBSTRING('|' + [Destination Prefix List], N+1, 255) AS Dest
	FROM tbl_MitsolRate
                CROSS JOIN N
	WHERE SUBSTRING('|' + [Destination Prefix List], N.N, 1) = '|'
)
,DestSplit
AS
(
	SELECT <tbl_MitsolRatePK>
		,CASE
			WHEN CHARINDEX('|', Dest) > 0
			THEN LEFT(Dest, CHARINDEX('|', Dest) -1)
			ELSE Dest
		END AS Dest
	FROM DestSplit1
)
INSERT INTO tbl_New_MitsolRate ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
[Time Based From Time], [Time Based To Time], [Destination Prefix List])
SELECT S.[Service Type], S.Origin, S.Destination, S.[Rate Per Minute], S.[Minimum Charge], S.[Time Based Rate], S.[Time Based From Day]
,S.[Time Based To Day], S.[Time Based From Time], S.[Time Based To Time], D.Dest
FROM tbl_MitsolRate S
	JOIN DestSplit D
		ON S.<tbl_MitsolRatePK> = D.<tbl_MitsolRatePK>;

quote:

---

Hmm, the Forum does not maintain my formatting

---

quote:

---

Originally posted by deonvisser

Basically

SERVICE ORIGIN DESTINATION DIAL PREFIX are the Col Names

As you can see, only the last col that contains the [DIAL PREFIX] has multiple Prefixes separated by |

Each of these prefixes must be split into new rows reatining the data of the other columns (SERVICE ORIGIN DESTINATION)

---

select yt.SERVICE
       , yt.ORIGIN
       , yt.DESTINATION
       , ca.outputCol as [prefix]
from   yourTable as yt
cross apply dbo.fn_split([DIAL PREFIX], '|') ca

select [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], [Destination Prefix List], ca.outputCol as [prefix]
from   tbl_MitsolRate as yt
cross apply dbo.split([Destination Prefix List], '|') ca


CREATE FUNCTION [dbo].[SPLIT] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX) 
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   SMALLINT IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN
    
      DECLARE @LENSTRING INT 
 
      WHILE LEN( @LIST ) > 0 
         BEGIN 
         
            SELECT @LENSTRING = 
               (CASE CHARINDEX( @DELIMITER, @LIST ) 
                   WHEN 0 THEN LEN( @LIST ) 
                   ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
                END
               ) 
                                
            INSERT INTO @TABLEOFVALUES 
               SELECT SUBSTRING( @LIST, 1, @LENSTRING )
                
            SELECT @LIST = 
               (CASE ( LEN( @LIST ) - @LENSTRING ) 
                   WHEN 0 THEN '' 
                   ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END 

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

INSERT INTO tbl_mitsolrate_new ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
                      [Time Based From Time], [Time Based To Time], [Destination Prefix List])
select t1.[service type], t1.Origin, t1.Destination, t1.[Rate Per Minute], t1.[Minimum Charge], t1.[Time Based Rate], t1.[Time Based From Day], t1.[Time Based To Day], 
                      t1.[Time Based From Time], t1.[Time Based To Time], s.items [Destination Prefix List]
from tbl_mitsolrate t1
outer apply dbo.split(t1.[Destination Prefix List], '|') s