CODEWITHSUNDEEP
t-sqlsql-server-2008function
Tom
Tom

Reputation: 1234

TSQL dbo.split including a key

So I have the function below. My question is how can I get the following query to use the function to to create a view. I'm trying to get p_c_id to be the @ValueID and notes to be the @List.

select p_c_id, notes from dbo.product

    create FUNCTION [dbo].[Split2Value]  
       (  @Delimiter varchar(5),   
          @List      varchar(8000),
          @ValueID   bigint  
       )   
       RETURNS @TableOfValues table   
          (  RowID   smallint IDENTITY(1,1),
             [Value] varchar(500),   
              ValueID bigint
          )   
    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 ), @ValueID 

                SELECT @List =   
                   (CASE ( len( @List ) - @LenString )   
                       WHEN 0 THEN ''   
                       ELSE right( @List, len( @List ) - @LenString - 1 )   
                    END  
                   )   
             END  

          RETURN   

       END

Upvotes: 0

Views: 693

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

select 
  SV.RowID,
  SV.[Value],
  ValueID 
from dbo.product as P
  cross apply dbo.Split2Value('DELI?', P.notes, P.p_c_id) as SV

Upvotes: 2

Related Questions