Back to OIM Explorer

dbo.QBM_FCVStringToList_i

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.756 characters

Interpretation

Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVStringToList source text reference

References

dbo.QBM_FCVStringToList

Referenced By

Complete Source

1CREATE FUNCTION dbo.QBM_FCVStringToList_i(2  @InString nvarchar(max),3  @Delimiter nvarchar(64),4  @SuppressEmpty BIT = 0,5  @CaseSensitive BIT = 0,6  @DelimiterInRow nvarchar(1)7) RETURNS @parameters TABLE(OrderNumber int identity,8Parameter1 nvarchar(max) collate database_Default,9Parameter2 nvarchar(max) collate database_Default,10OrderNumberReverse int11)12  WITH schemabinding13AS14BEGIN15  DECLARE @i int16  DECLARE @l int17  DECLARE @SoFar nvarchar(max)18  DECLARE @char nvarchar(64)19  DECLARE @CountElements int = 020  DECLARE @charindex_DelimiterInRow_Sofar int21  DECLARE @lenDelim int22  SELECT @lenDelim = len(@Delimiter)23  IF isnull(@InString,24  N '') = N ''25  BEGIN26    GOTO ende27  END28  IF trim(isnull(convert(nvarchar(64), @Delimiter), N '')) = N ''29  BEGIN30    IF len(@Delimiter + '#') > 131    BEGIN32      SELECT @lenDelim = len(@Delimiter + '#') - 133    END34    ELSE35    BEGIN36      SELECT @Delimiter = nchar(7)37      SELECT @lenDelim = 138    END39  END40  IF @lendelim = 141  BEGIN42    IF @CaseSensitive = 043    BEGIN44      IF @SuppressEmpty = 145      BEGIN46        INSERT INTO @parameters(Parameter1)47        SELECT trim(x.value)48        FROM string_split(@InString,49        @Delimiter) x50        WHERE51          x.value > ' '52        SELECT @CountElements = @@ROWCOUNT53      END54      ELSE55      BEGIN56        INSERT INTO @parameters(Parameter1)57        SELECT trim(x.value)58        FROM string_split(@InString,59        @Delimiter) x60        SELECT @CountElements = @@ROWCOUNT61      END62    END63    ELSE64    BEGIN65      IF @SuppressEmpty = 166      BEGIN67        INSERT INTO @parameters(Parameter1)68        SELECT trim(x.value)69        FROM string_split(@InString,70        @Delimiter collate SQL_Latin1_General_CP1_CS_AS) x71        WHERE72          x.value > ' '73        SELECT @CountElements = @@ROWCOUNT74      END75      ELSE76      BEGIN77        INSERT INTO @parameters(Parameter1)78        SELECT trim(x.value)79        FROM string_split(@InString,80        @Delimiter collate SQL_Latin1_General_CP1_CS_AS) x81        SELECT @CountElements = @@ROWCOUNT82      END83    END84    IF @DelimiterInRow > ' '85    BEGIN86      UPDATE @parameters87      SET Parameter1 = trim(substring(Parameter1, 1, charindex(@DelimiterInRow, Parameter1) -1)),88      Parameter2 = trim(substring(Parameter1, charindex(@DelimiterInRow, Parameter1) +1, len(Parameter1)))89      WHERE90        charindex(@DelimiterInRow,91      Parameter1) > 092    END93    GOTO setReverse94  END95  SELECT @l = len(@InString)96  SELECT @Sofar = N ''97  SELECT @i = 198  WHILE @i <= @l99  BEGIN100    SELECT101      @Char = substring(@InString,102      @i,103      @lenDelim)104    IF(@CaseSensitive = 1 AND @Char = @Delimiter collate SQL_Latin1_General_CP1_CS_AS) OR(@CaseSensitive = 0 AND @Char = @Delimiter collate SQL_Latin1_General_CP1_CI_AS105    )106    BEGIN107      SELECT @Sofar = trim(@Sofar)108      IF @Sofar > ' ' OR @SuppressEmpty = 0109      BEGIN110        IF @DelimiterInRow > ' '111        BEGIN112          SELECT113            @charindex_DelimiterInRow_Sofar = charindex(@DelimiterInRow,114            @Sofar)115          IF @charindex_DelimiterInRow_Sofar > 0116          BEGIN117            INSERT INTO @parameters(Parameter1,118            Parameter2)119            SELECT120              substring(@Sofar,121              1,122              @charindex_DelimiterInRow_Sofar-1),123              substring(@Sofar,124              @charindex_DelimiterInRow_Sofar + 1,125              len(@sofar))126          END127          ELSE128          BEGIN129            INSERT INTO @parameters(Parameter1)130            VALUES(@Sofar)131          END132        END133        ELSE134        BEGIN135          INSERT INTO @parameters(Parameter1)136          VALUES(@Sofar)137        END138        SELECT @CountElements += 1139      END140      SELECT @SoFar = N ''141      SELECT @i += @lenDelim142    END143    ELSE144    BEGIN145      SELECT146        @Sofar = CONCAT(@Sofar,147        LEFT(@char, 1))148      SELECT @i += 1149    END150  END151  SELECT @Sofar = trim(@Sofar)152  IF @Sofar > ' '153  BEGIN154    IF @DelimiterInRow > ' '155    BEGIN156      SELECT157        @charindex_DelimiterInRow_Sofar = charindex(@DelimiterInRow,158        @Sofar)159      IF @charindex_DelimiterInRow_Sofar > 0160      BEGIN161        INSERT INTO @parameters(Parameter1,162        Parameter2)163        SELECT164          substring(@Sofar,165          1,166          @charindex_DelimiterInRow_Sofar-1),167          substring(@Sofar,168          @charindex_DelimiterInRow_Sofar + 1,169          len(@sofar))170      END171      ELSE172      BEGIN173        INSERT INTO @parameters(Parameter1)174        VALUES(@Sofar)175      END176    END177    ELSE178    BEGIN179      INSERT INTO @parameters(Parameter1)180      VALUES(@Sofar)181    END182    SELECT @CountElements += 1183  END184  setReverse:185  UPDATE @parameters186  SET OrderNumberReverse = @CountElements - Ordernumber +1 ende:187  RETURN188END

Open raw exported source

1create function dbo.QBM_FCVStringToList_i(@InString nvarchar(max) , @Delimiter nvarchar(64) , @SuppressEmpty bit = 0 , @CaseSensitive bit = 0 , @DelimiterInRow2 nvarchar(1) ) returns @parameters table( OrderNumber int identity , Parameter1 nvarchar(max) collate database_Default , Parameter2 nvarchar(max) collate3 database_Default , OrderNumberReverse int ) with schemabinding as begin  declare @i int declare @l int declare @SoFar nvarchar(max) declare @char nvarchar4(64) declare @CountElements int = 0 declare @charindex_DelimiterInRow_Sofar int declare @lenDelim int select @lenDelim = len(@Delimiter) if isnull(@InString5, N'') = N'' begin goto ende end if trim(isnull(convert(nvarchar(64), @Delimiter), N'')) = N'' begin if len(@Delimiter + '#') > 1  begin select @lenDelim6 = len(@Delimiter + '#') - 1 end else begin    select @Delimiter = nchar(7) select @lenDelim = 1 end end  if @lendelim = 1 begin  if @CaseSensitive = 07 begin if @SuppressEmpty = 1 begin insert into @parameters(Parameter1) select trim(x.value) from string_split (@InString, @Delimiter) x where x.value >8 ' ' select @CountElements = @@ROWCOUNT end else begin insert into @parameters(Parameter1) select trim(x.value) from string_split (@InString, @Delimiter9) x select @CountElements = @@ROWCOUNT end end else begin if @SuppressEmpty = 1 begin insert into @parameters(Parameter1) select trim(x.value) from string_split10 (@InString, @Delimiter collate SQL_Latin1_General_CP1_CS_AS ) x where x.value > ' ' select @CountElements = @@ROWCOUNT end else begin insert into @parameters11(Parameter1) select trim(x.value) from string_split (@InString, @Delimiter collate SQL_Latin1_General_CP1_CS_AS ) x select @CountElements = @@ROWCOUNT 12end end if @DelimiterInRow > ' ' begin  update @parameters set Parameter1 = trim(substring(Parameter1, 1, charindex(@DelimiterInRow, Parameter1)-1) ) ,13 Parameter2 = trim(substring(Parameter1, charindex(@DelimiterInRow, Parameter1) +1 , len(Parameter1))) where charindex(@DelimiterInRow, Parameter1) > 014 end goto setReverse end select @l = len(@InString) select @Sofar = N'' select @i = 1 while @i <= @l begin select @Char = substring(@InString, @i, @lenDelim15)  if ( @CaseSensitive = 1 and @Char = @Delimiter collate SQL_Latin1_General_CP1_CS_AS ) or (@CaseSensitive = 0 and @Char = @Delimiter collate SQL_Latin1_General_CP1_CI_AS16 ) begin select @Sofar = trim(@Sofar) if @Sofar > ' ' or @SuppressEmpty = 0 begin if @DelimiterInRow > ' ' begin select @charindex_DelimiterInRow_Sofar17 = charindex(@DelimiterInRow, @Sofar) if @charindex_DelimiterInRow_Sofar > 0 begin insert into @parameters(Parameter1, Parameter2) select substring(@Sofar18, 1, @charindex_DelimiterInRow_Sofar-1), substring(@Sofar, @charindex_DelimiterInRow_Sofar + 1, len(@sofar)) end else begin insert into @parameters(Parameter119) values( @Sofar) end end else begin insert into @parameters(Parameter1) values( @Sofar) end  select @CountElements += 1 end select @SoFar = N'' select20 @i += @lenDelim end else begin select @Sofar = concat(@Sofar , left(@char, 1)) select @i += 1 end  end select @Sofar = trim(@Sofar) if @Sofar > ' ' begin21 if @DelimiterInRow > ' ' begin select @charindex_DelimiterInRow_Sofar = charindex(@DelimiterInRow, @Sofar) if @charindex_DelimiterInRow_Sofar > 0 begin22 insert into @parameters(Parameter1, Parameter2) select substring(@Sofar, 1, @charindex_DelimiterInRow_Sofar-1), substring(@Sofar, @charindex_DelimiterInRow_Sofar23 + 1, len(@sofar)) end else begin insert into @parameters(Parameter1) values( @Sofar) end end else begin insert into @parameters(Parameter1) values( @Sofar24) end  select @CountElements += 1 end setReverse: update @parameters set OrderNumberReverse = @CountElements - Ordernumber +1 ende: return end 25