Split Text Into different Columns :

Create TempProc(@ReferenceNumber Varchar(100)) As

Declare @Col1 as Varchar(20)
Declare @Col2 as Varchar(20)
Declare @Col3 as Varchar(20)
Declare @Col4 as Varchar(20)
Declare @TempVal as Varchar(100)

Set @Col1 = Left(@ReferenceNumber,PatIndex('%/%',@ReferenceNumber) - 1)
Set @TempVal = Right(@ReferenceNumber,len(@REferenceNumber) - PatIndex('%/%'@ReferenceNumber))

Set @Col2 = Left(@TempVal,PatIndex('%/%',@TempVal) - 1)
Set @TempVal = Right(@TempVal,len(@TempVal) - PatIndex('%/%'@TempVal))

Set @Col3 = Left(@TempVal,PatIndex('%/%',@TempVal) - 1)
Set @TempVal = Right(@TempVal,len(@TempVal) - PatIndex('%/%'@TempVal))

Set @Col4 = @TempVal

Select @Col1,@Col2,@Col3,@Col4

Return

drop function dbo.CSV_Values
go
create function dbo.CSV_Values(@str varchar(1000))
returns
   @Table Table (value varchar(100))
 as
begin      
      
      declare @ptr as smallint,
            @counter smallint
      declare @substr varchar(1000)
      set @substr = ''
      set @ptr = 1
      set @counter = 0
      while @ptr <=len (@str)+1
            begin
            
            if (substring(@str, @ptr,  1) = '/' ) or @ptr = len(@str) +1
            begin

                  
                  insert into @table values(substring(@str,len(@substr)+1,@counter))      
                  set @substr = substring(@str,1,@ptr)
                  set @counter = 0              
            end
                  else
                  set @counter = @counter +1
                  set @ptr = @ptr + 1
            end
      Return
end

go

SELECT dbo.csv_values(ReferenceNumber),  .........  FROM Table1 WHERE id=1

Hi Imran,

Your function is created successfully but when I try to run, I get the below message.

SELECT dbo.csv_values('AB/12/DE/CE')

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.csv_values'.

What am I missing?


Regards
Wayne

Hi Wayne,

Did you tried my solution???

Does it work??

Regards,

dduser

Hi ddUser,

Somehow your PROC/FUNC is not getting created due to the below errors.
can you correct so that I can try again?

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@ReferenceNumber'.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@TempVal'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '@TempVal'.

Wayne

Ok you missed the commas. now it runs atleast.

Hi,
it is returning a table try
SELECT * FROM dbo.csv_values('AB/12/DE/CE')

Hi Imran,

Thanks. But I need them as Columns. It is returning rows.
Wayne

Create Procedure TempProc(@ReferenceNumber Varchar(100)) As

Declare @Col1 as Varchar(20)
Declare @Col2 as Varchar(20)
Declare @Col3 as Varchar(20)
Declare @Col4 as Varchar(20)
Declare @TempVal as Varchar(100)

Set @Col1 = Left(@ReferenceNumber,PatIndex('%/%',@ReferenceNumber) - 1)
Set @TempVal = Right(@ReferenceNumber,len(@REferenceNumber) - PatIndex('%/%'@ReferenceNumber))

Set @Col2 = Left(@TempVal,PatIndex('%/%',@TempVal) - 1)
Set @TempVal = Right(@TempVal,len(@TempVal) - PatIndex('%/%',@TempVal))

Set @Col3 = Left(@TempVal,PatIndex('%/%',@TempVal) - 1)
Set @TempVal = Right(@TempVal,len(@TempVal) - PatIndex('%/%',@TempVal))

Set @Col4 = @TempVal

Select @Col1,@Col2,@Col3,@Col4

Return

Now it workss????

Yes in the 1st PatIndex, a comma is required.

You are very close to my solution.

Consider, I already have an existing SELECT statement that is retrieving 10 fields from Various tables using Joins etc.

Now How can I get these additional col1,col2,col3,col4 in the same select statement as it is a PROC.

Wayne

Many Thanks DdUser.

Sorry. Thanks to Imran too.