问:
FileTable Delimited String Split

EDIT - To be clear I will put the goal sentence at the top. The test
  and my question is whether there is a way to get the same performance
  as a temp table without using a temp table.

I feel this should be an easy question but I am stuck.  I am experimenting with FileTables in SQL2014.  I know of a few alternatives that would work well but the goal is to establish feasibility of extracting substrings of text from a filetable.

This test has 35,000 text files with one line of text as follows with each file having an average of 100 bytes of non-unicode text.

```<code>Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg
</code>```

The desired output is one row for each file and the delimited string to be split into seven columns.

I have found a quick string parser function, but running on the filestream has a significant performance impact compared to a varchar column.

This query takes 18 seconds to run.  I am trying to have the conversion from filestream to varchar execute only once but I think calling the UDF may be causing it to happen for every row(file).

```<code>Create View vAddresses As
Select file_type, Convert(Varchar(8000),file_stream) TextData /* Into #Temp */ From InputFiles Where file_type = 'adr'
Go
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From vAddresses
</code>```

I have tried it as a view, a cte and a subquery.  The only thing that seems to help is creating a temp table.  Creating the temp table takes 1 second and the query takes one second.  So for 35k rows 2 seconds total query time vs. 18 seconds.

```<code>Drop Table #Temp
(Select file_type, Convert(Varchar(8000),file_stream) TextData Into #Temp From HumanaInputFiles Where file_type = 'adr')
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From #Temp
</code>```

I have read many posts and blogs on both the topics of filetables and temp table vs single query performance but I cant seem to figure it out.  It might have something to do with sargable or statistics?  Any advice is much appreciated.

Here is the UDF, I found it on an MSDN blog / forum and is the best performer I have found so far.

```<code>ALTER FUNCTION [dbo].[udf_StringSplit](
 @TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END
</code>```

This is the execution plan for the temp table.

```<code><?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.166487" StatementText="Select --TextData,
\n       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
\n      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
\n      dbo.udf_StringSplit(TextData, 7, '|')--, TextData
\n From #Temp" StatementType="SELECT" QueryHash="0xC4D6F0215D332F3D" QueryPlanHash="0xC50CFAF9494B5DBE" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.166487">
              <OutputList>
                <ColumnReference Column="Expr1003" />
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4011" EstimateCPU="0.0193131" EstimateIO="0.145426" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.164739" TableCardinality="17486">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" IndexKind="Heap" Storage="RowStore" />
                  </TableScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
</code>```

This is the plan for the view.

```<code><?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.905265" StatementText="Select    --TextData,
\n       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
\n      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
\n      dbo.udf_StringSplit(TextData, 7, '|')--, TextData
\n From vAddresses" StatementType="SELECT" QueryHash="0xB4F8A0B288802C4E" QueryPlanHash="0x28DA02D774B1AF53" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="520">
            <Warnings>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.905265">
              <OutputList>
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
                <ColumnReference Column="Expr1010" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1010" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4019" EstimateCPU="0.0034972" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.88673">
                  <OutputList>
                    <ColumnReference Column="Expr1011" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1011" />
                        <ScalarOperator ScalarString="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)">
                          <Convert DataType="varchar" Length="8000" Style="0" Implicit="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                              </Identifier>
                            </ScalarOperator>
                          </Convert>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="4043" EstimateCPU="0.0386262" EstimateIO="0.844606" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.883233" TableCardinality="34972">
                      <OutputList>
                        <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" IndexKind="Heap" Storage="RowStore" />
                        <Predicate>
                          <ScalarOperator ScalarString="[DmProd01].[dbo].[HumanaInputFiles].[file_type]=N'adr'">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_type" ComputedColumn="true" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="N'adr'" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </TableScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
</code>```

EDIT: Searched on this differently and found an answer is to use top and order by.  This got it down to 4 seconds.  Seems kind of hokey and still dont explain how looking at query plans helps figure this out, so not going to answer this myself, instead leave it open.

答:
[1] I've used many times following solution that (1.1) it converts source strings to XML and then (1.2) it extract every time from XMl using value method thus:

```<code>USE tempdb
GO
IF OBJECT_ID('dbo.SourceTable') IS NOT NULL
    DROP TABLE dbo.SourceTable
GO
CREATE TABLE dbo.SourceTable (
    ID      INT IDENTITY PRIMARY KEY,
    Col1    VARCHAR(100) NOT NULL
);
INSERT  dbo.SourceTable (Col1) VALUES ('Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg')
INSERT  dbo.SourceTable (Col1) VALUES ('hhh|iii|JJJ|kkk')

SELECT  b.ID, c.XmlCol.value('.', 'VARCHAR(100)') AS ItemVal--, ROW_NUMBER() OVER(PARTITION BY b.ID ORDER BY c.XmlCol) AS RowNum
FROM (
    SELECT  a.ID, CONVERT(XML, '<root><i>' + REPLACE(a.Col1, '|', '</i><i>') + '</i></root>') AS Col1AsXML
    FROM    dbo.SourceTable a
) b OUTER APPLY b.Col1AsXML.nodes('root/i') c(XmlCol)
--OPTION(FORCE ORDER)
</code>```

[2] I would to some performance test if you provide DDL & DML statements.

[3] It could help if you could provide following details

There is a maximum number of items for every string ?

If all items have the same length (ex. 3 chars) ?

Title: FileTable Delimited String Split

Body:
EDIT - To be clear I will put the goal sentence at the top. The test
  and my question is whether there is a way to get the same performance
  as a temp table without using a temp table.

I feel this should be an easy question but I am stuck.  I am experimenting with FileTables in SQL2014.  I know of a few alternatives that would work well but the goal is to establish feasibility of extracting substrings of text from a filetable.

This test has 35,000 text files with one line of text as follows with each file having an average of 100 bytes of non-unicode text.

Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg

The desired output is one row for each file and the delimited string to be split into seven columns.

I have found a quick string parser function, but running on the filestream has a significant performance impact compared to a varchar column.

This query takes 18 seconds to run.  I am trying to have the conversion from filestream to varchar execute only once but I think calling the UDF may be causing it to happen for every row(file).

Create View vAddresses As
Select file_type, Convert(Varchar(8000),file_stream) TextData /* Into #Temp */ From InputFiles Where file_type = 'adr'
Go
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From vAddresses

I have tried it as a view, a cte and a subquery.  The only thing that seems to help is creating a temp table.  Creating the temp table takes 1 second and the query takes one second.  So for 35k rows 2 seconds total query time vs. 18 seconds.

Drop Table #Temp
(Select file_type, Convert(Varchar(8000),file_stream) TextData Into #Temp From HumanaInputFiles Where file_type = 'adr')
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From #Temp

I have read many posts and blogs on both the topics of filetables and temp table vs single query performance but I cant seem to figure it out.  It might have something to do with sargable or statistics?  Any advice is much appreciated.

Here is the UDF, I found it on an MSDN blog / forum and is the best performer I have found so far.

ALTER FUNCTION [dbo].[udf_StringSplit](
 @TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END

This is the execution plan for the temp table.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.166487" StatementText="Select --TextData,
       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
      dbo.udf_StringSplit(TextData, 7, '|')--, TextData
 From #Temp" StatementType="SELECT" QueryHash="0xC4D6F0215D332F3D" QueryPlanHash="0xC50CFAF9494B5DBE" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.166487">
              <OutputList>
                <ColumnReference Column="Expr1003" />
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4011" EstimateCPU="0.0193131" EstimateIO="0.145426" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.164739" TableCardinality="17486">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" IndexKind="Heap" Storage="RowStore" />
                  </TableScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

This is the plan for the view.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.905265" StatementText="Select    --TextData,
       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
      dbo.udf_StringSplit(TextData, 7, '|')--, TextData
 From vAddresses" StatementType="SELECT" QueryHash="0xB4F8A0B288802C4E" QueryPlanHash="0x28DA02D774B1AF53" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="520">
            <Warnings>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.905265">
              <OutputList>
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
                <ColumnReference Column="Expr1010" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1010" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4019" EstimateCPU="0.0034972" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.88673">
                  <OutputList>
                    <ColumnReference Column="Expr1011" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1011" />
                        <ScalarOperator ScalarString="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)">
                          <Convert DataType="varchar" Length="8000" Style="0" Implicit="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                              </Identifier>
                            </ScalarOperator>
                          </Convert>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="4043" EstimateCPU="0.0386262" EstimateIO="0.844606" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.883233" TableCardinality="34972">
                      <OutputList>
                        <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" IndexKind="Heap" Storage="RowStore" />
                        <Predicate>
                          <ScalarOperator ScalarString="[DmProd01].[dbo].[HumanaInputFiles].[file_type]=N'adr'">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_type" ComputedColumn="true" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="N'adr'" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </TableScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

EDIT: Searched on this differently and found an answer is to use top and order by.  This got it down to 4 seconds.  Seems kind of hokey and still dont explain how looking at query plans helps figure this out, so not going to answer this myself, instead leave it open.

Body:
[1] I've used many times following solution that (1.1) it converts source strings to XML and then (1.2) it extract every time from XMl using value method thus:

USE tempdb
GO
IF OBJECT_ID('dbo.SourceTable') IS NOT NULL
    DROP TABLE dbo.SourceTable
GO
CREATE TABLE dbo.SourceTable (
    ID      INT IDENTITY PRIMARY KEY,
    Col1    VARCHAR(100) NOT NULL
);
INSERT  dbo.SourceTable (Col1) VALUES ('Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg')
INSERT  dbo.SourceTable (Col1) VALUES ('hhh|iii|JJJ|kkk')

SELECT  b.ID, c.XmlCol.value('.', 'VARCHAR(100)') AS ItemVal--, ROW_NUMBER() OVER(PARTITION BY b.ID ORDER BY c.XmlCol) AS RowNum
FROM (
    SELECT  a.ID, CONVERT(XML, '<root><i>' + REPLACE(a.Col1, '|', '</i><i>') + '</i></root>') AS Col1AsXML
    FROM    dbo.SourceTable a
) b OUTER APPLY b.Col1AsXML.nodes('root/i') c(XmlCol)
--OPTION(FORCE ORDER)

[2] I would to some performance test if you provide DDL & DML statements.

[3] It could help if you could provide following details

There is a maximum number of items for every string ?

If all items have the same length (ex. 3 chars) ?


来源: stackexchange

问:
Design patterns to avoid
A lot of people seem to agree, that the Singleton pattern has a number of drawbacks and some even suggest avoiding the pattern entirely. There's an excellent discussion here. Please direct any comments about the Singleton pattern to that question.

My question: Are there other design patterns, that should be avoided or used with great care?

答:
I don't think you should avoid Design Patterns (DP), and I don't think you should force yourself to use DPs when planning your architecture. We should only use DPs when they natural emerge from our planning.

If we define from the start that we want to use a given DP, many of our future design decisions will be influence by that choice, with no guarantee that the DP we chose is suited for our needs.

One thing we also shouldn't do is treat a DP as an immutable entity, we should adapt the pattern to our needs.

So, sumarizing, I don't think we should avoid DPs, we should embrace them when they are already taking shape in our architecture.

OwnerDisplayName: Brian Rasmussen

ContentLicense: CC BY-SA 2.5

Title: Design patterns to avoid

LastEditorDisplayName: Joel Coehoorn


问:
How can I select one column from an SQL query with the "SHOW" keyword?
How can I select one column from this SQL query:

```<code>SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'
</code>```

like this:

```<code>SELECT name FROM table WHERE id = 1
</code>```

This code worked for me

```<code>SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
 AND t.table_schema='YOURDATABASE'
 AND t.table_name='YOURTABLE';
</code>```

答:
If your goal is to fetch the column name of primary keys, you can use this query which gets the information from the information_schema.statistics table. You can filter the information by schema / table name as well.</p>

```<code>SELECT column_name
FROM information_schema.statistics
WHERE table_schema='schema_name'
AND table_name = 'table_name'
AND index_name='PRIMARY'
ORDER BY seq_in_index;
</code>```

ContentLicense: CC BY-SA 3.0

Body:
How can I select one column from this SQL query:

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

like this:

SELECT name FROM table WHERE id = 1

This code worked for me

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
 AND t.table_schema='YOURDATABASE'
 AND t.table_name='YOURTABLE';

Body:
If your goal is to fetch the column name of primary keys, you can use this query which gets the information from the information_schema.statistics table. You can filter the information by schema / table name as well.

SELECT column_name
FROM information_schema.statistics
WHERE table_schema='schema_name'
AND table_name = 'table_name'
AND index_name='PRIMARY'
ORDER BY seq_in_index;


问:
SQLite - No error when updating a deleted row
I'm trying to force an error condition to test some exception handling by updating a row after it has been deleted using the following steps:

```<code>1. Load record into GUI
2. Pull .db3 file to Windows
3. Delete record with sqlite3
4. Push .db3 file back to Android
3. Save record from GUI
</code>```

However, there is no exception thrown from ExecuteNonQuery() nor is there anything in logcat.

Why, if for Step 3, the DB file is instead renamed an exception thrown, but not when deleting the record?

Update: Deleting the record directly from the device with sqlite3 results in this same behavior.

Note: Samsung Galaxy Tab2 requires the PIE (Position Independent Execution) version.

答:
Updating a row doesn't throw any error/exception in sql.
when a row is not found in database update query does nothing and show no error/exception message.</p>

Body:
I'm trying to force an error condition to test some exception handling by updating a row after it has been deleted using the following steps:

1. Load record into GUI
2. Pull .db3 file to Windows
3. Delete record with sqlite3
4. Push .db3 file back to Android
3. Save record from GUI

However, there is no exception thrown from ExecuteNonQuery() nor is there anything in logcat.

Why, if for Step 3, the DB file is instead renamed an exception thrown, but not when deleting the record?

Update: Deleting the record directly from the device with sqlite3 results in this same behavior.

Note: Samsung Galaxy Tab2 requires the PIE (Position Independent Execution) version.


问:
comma separated regex
I have an email regex I am happy with, lets call it EMAIL.
So my field validator looks like this

```<code>var regex=/^EMAIL$/
</code>```

But now the Captain tells me he wants to allow comma separated emails in the field.

What first comes to mind is something like

```<code>/^(EMAIL)?(, EMAIL)*$/
</code>```

but that repeats EMAIL, which already offends my senses.  Is there a way to write this regex without repeating EMAIL?

答:
I can't think of a way to match email list as it is, without repeating EMAIL. Here is a simple workaround:

/^(EMAIL, )+$/.test(emailList + ", ");

Example:

```<code>var s = "abc, def, ghi, jkl";
/^([a-z]+, )+$/.test(s + ", ");
</code>```

Title: comma separated regex

Body:
I have an email regex I am happy with, lets call it EMAIL.
So my field validator looks like this

var regex=/^EMAIL$/

But now the Captain tells me he wants to allow comma separated emails in the field.

What first comes to mind is something like

/^(EMAIL)?(, EMAIL)*$/

but that repeats EMAIL, which already offends my senses.  Is there a way to write this regex without repeating EMAIL?

Body:
I can't think of a way to match email list as it is, without repeating EMAIL. Here is a simple workaround:

/^(EMAIL, )+$/.test(emailList + ", ");

Example:

var s = "abc, def, ghi, jkl";
/^([a-z]+, )+$/.test(s + ", ");


问:
column with dates into datetime index in dask
```<code>pd.DatetimeIndex(df_dask_dataframe['name_col'])
</code>```

I have a dask dataframe for which I want to convert a column with dates into datetime index. However I get a not implemented error. Is there a workaround?

答:
I think you need dask.dataframe.DataFrame.set_index if dtype of column is datetime64:

```<code>df_dask_dataframe = df_dask_dataframe.set_index('name_col')
</code>```

Body:
pd.DatetimeIndex(df_dask_dataframe['name_col'])

I have a dask dataframe for which I want to convert a column with dates into datetime index. However I get a not implemented error. Is there a workaround?

Body:
I think you need dask.dataframe.DataFrame.set_index if dtype of column is datetime64:

df_dask_dataframe = df_dask_dataframe.set_index('name_col')


问:
AngularJS input check/uncheck with 2 functions
I have a checkbox.

Check -> call displayStuff();

Uncheck -> call hideStuff();

I tried something like that but it doesn't work:

```<code><input type="checkbox" id="mycheckbox" name="mycheckbox" ng-model="mycheckbox" data-ng-change="myCheckbox ? displayStuff() : hideStuff()" />
</code>```

Thanks for your help!

答:
HTML:

```<code>    <input type="checkbox" ng-change="checked()"  ng-model="mycheckbox" />
</code>```

Controller:

$scope.mycheckbox = false;

```<code>    $scope.checked = function () {
        if ($scope.mycheckbox)
            $scope.hideStuff();
        else
            $scope.displayStuff();

    }

     $scope.displayStuff=function(){
         alert("d");
         $scope.mycheckbox=true;

    }

    $scope.hideStuff= function() {
        alert("h");
        $scope.mycheckbox=false;
    }
</code>```

Body:
I have a checkbox.

Check -> call displayStuff();

Uncheck -> call hideStuff();

I tried something like that but it doesn't work:

<input type="checkbox" id="mycheckbox" name="mycheckbox" ng-model="mycheckbox" data-ng-change="myCheckbox ? displayStuff() : hideStuff()" />

Thanks for your help!

Body:
HTML:

    <input type="checkbox" ng-change="checked()"  ng-model="mycheckbox" />

Controller:

$scope.mycheckbox = false;

    $scope.checked = function () {
        if ($scope.mycheckbox)
            $scope.hideStuff();
        else
            $scope.displayStuff();

    }

     $scope.displayStuff=function(){
         alert("d");
         $scope.mycheckbox=true;

    }

    $scope.hideStuff= function() {
        alert("h");
        $scope.mycheckbox=false;
    }


问:
send html template in php
My Php mail code is like this i want to send html content in mail but i can not send html template with this code

```<code>                    $to = $email;;
                    $subject = 'New Order Detail';
                    $message = 'Hi Jane';
                    $from = 'info@abc.co.in';

                    // Sending email
                    if(mail($to, $subject, $message))
                    {
                        echo 'Your mail has been sent successfully.';
                    }
                    else
                    {
                        echo 'Unable to send email. Please try again.';
                        $headers  = 'MIME-Version: 1.0' . "\r\n";
                        $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

                        // Create email headers
                        $headers .= 'From: '.$from."\r\n".
                            'Reply-To: '.$from."\r\n" .
                            'X-Mailer: PHP/' . phpversion();

                        // Compose a simple HTML email message
                        $message = '<html><body>';
                        $message = '<table border="1" align="center" width="50%">';
                        $message .= '<tr><td>Test Mail';
                        $message .= '</td></tr>';
                        //$message .= '<p style="color:#080;font-size:18px;">Will you marry me?</p>';
                        $message .= '</table>';
                        $message .= '</body></html>';

                        // Sending email
                        if(mail($to, $subject, $message, $headers))
                        {
                            echo 'Your mail has been sent successfully.';
                        } else
                        {
                            echo 'Unable to send email. Please try again.';
                        }
                    }
</code>```

This kind of output i get in mail i do not know where is my mistake in this code..
Output

```<code><html><body>
<table border="1" align="center" width="50%">
<tr><td>Test Mail</td></tr>
</table>
</body></html>
</code>```

答:
add proper header contents.</p>

```<code>  // Compose a simple HTML email message
              $message = '<html><body>';
              $message = '<table border="1" align="center" width="50%">';
              $message .= '<tr><td>Test Mail';
              $message .= '</td></tr>';
              //$message .= '<p style="color:#080;font-size:18px;">Will you marry me?</p>';
              $message .= '</table>';
              $message .= '</body></html>';

 $headers[] = 'MIME-Version: 1.0';
 $headers[] = 'Content-type: text/html; charset=iso-8859-1';
// Additional headers
$headers[] = 'To: abc <abc@example.com>, xyz <xyz@example.com>';
$headers[] = 'From: ABC <Abc@example.com>';
$headers[] = 'Cc: abc@example.com';
$headers[] = 'Bcc: xyz@example.com';

 // Mail it
 mail($to, $subject, $message, implode("\r\n", $headers));
</code>```

Title: send html template in php

Body:
My Php mail code is like this i want to send html content in mail but i can not send html template with this code

                    $to = $email;;
                    $subject = 'New Order Detail';
                    $message = 'Hi Jane';
                    $from = 'info@abc.co.in';

                    // Sending email
                    if(mail($to, $subject, $message))
                    {
                        echo 'Your mail has been sent successfully.';
                    }
                    else
                    {
                        echo 'Unable to send email. Please try again.';
                        $headers  = 'MIME-Version: 1.0' . "\r\n";
                        $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

                        // Create email headers
                        $headers .= 'From: '.$from."\r\n".
                            'Reply-To: '.$from."\r\n" .
                            'X-Mailer: PHP/' . phpversion();

                        // Compose a simple HTML email message
                        $message = '';
                        $message = '
';
                        $message .= '
Test Mail';
                        $message .= '
';
                        //$message .= '
Will you marry me?
';
                        $message .= '
';
                        $message .= '';

                        // Sending email
                        if(mail($to, $subject, $message, $headers))
                        {
                            echo 'Your mail has been sent successfully.';
                        } else
                        {
                            echo 'Unable to send email. Please try again.';
                        }
                    }

This kind of output i get in mail i do not know where is my mistake in this code..
Output

Test Mail

Body:
add proper header contents.

  // Compose a simple HTML email message
              $message = '<html><body>';
              $message = '<table border="1" align="center" width="50%">';
              $message .= '<tr><td>Test Mail';
              $message .= '</td></tr>';
              //$message .= '<p style="color:#080;font-size:18px;">Will you marry me?</p>';
              $message .= '</table>';
              $message .= '</body></html>';

 $headers[] = 'MIME-Version: 1.0';
 $headers[] = 'Content-type: text/html; charset=iso-8859-1';
// Additional headers
$headers[] = 'To: abc <abc@example.com>, xyz <xyz@example.com>';
$headers[] = 'From: ABC <Abc@example.com>';
$headers[] = 'Cc: abc@example.com';
$headers[] = 'Bcc: xyz@example.com';

 // Mail it
 mail($to, $subject, $message, implode("\r\n", $headers));


问:
Pagination not working on multiple querying
I have a multiple query, but for some reason the pagination is not working, i dont no if the ussue is the way i construct it.

My code:

```<code> $query = \DB::table('products');

   if ($request->has('type') && $type) {
        $query->where('product_type_id', $type);
    }

    if ($request->has('material') && $material) {
      $query->join("product_attribute_materials","products.id","=","product_attribute_materials.product_id")
        ->whereIn("product_attribute_materials.product_material_id", $material);
    }

    if($request->has('businessarea') && $area){
        $query->join("product_attribute_businessareas","products.id","=","product_attribute_businessareas.product_id")
        ->whereIn("product_attribute_businessareas.product_businessarea_id", $area);
    }

    $products = $query->paginate(15);
</code>```

答:
You need to append the query string to the pagination using append() on $products

Passing array with the keys that you want to pass the query string, will return its value on the next request.

To confirm this, chick the href value from your browser before clicking on page number 2.

    $products = $query->paginate(15)->appends(['type', 'material', 'businessarea']);

Body:
I have a multiple query, but for some reason the pagination is not working, i dont no if the ussue is the way i construct it.

My code:

 $query = \DB::table('products');

   if ($request->has('type') && $type) {
        $query->where('product_type_id', $type);
    }

    if ($request->has('material') && $material) {
      $query->join("product_attribute_materials","products.id","=","product_attribute_materials.product_id")
        ->whereIn("product_attribute_materials.product_material_id", $material);
    }

    if($request->has('businessarea') && $area){
        $query->join("product_attribute_businessareas","products.id","=","product_attribute_businessareas.product_id")
        ->whereIn("product_attribute_businessareas.product_businessarea_id", $area);
    }

    $products = $query->paginate(15);


问:
How to transform a JSON message in XML in WSO2
I am relative new in WSO2 services in Java EE. I started by the Tutorial, but i had some little difficulties because my lack of knowledge about services in general.
My problem now is that i am trying to send a JSON message and receive the same message in XML format. I studied in the site and they talk about Formatter and Builder, but i had a lot of difficult to apply that in my project.
I already tried with Data Mapper, putting him before the send in the return, but this always return the timeout message. And i tried with Payload Factory too, but i thinking that i am doing something wrong, because the same happens.
Someone know a tutorial or good video detailed to i be able to learn how to solve this problem?

If someone could help me i would stay quiet grateful. Thank you.

答:
i hope you are expected to transform JSON to XML.

In order to achieve this, you can simply create an API like below

<api xmlns="http://ws.apache.org/ns/synapse" name="JsonToXmlApi" context="/Test">
   <resource methods="POST" uri-template="/xml/">
      <inSequence>
         <property name="messageType" value="text/xml" scope="axis2"/>

         <property name="RESPONSE" value="true" scope="default" type="STRING"/>
         <send/>
      </inSequence>
   </resource>
</api>
                        ```

Body:
i hope you are expected to transform JSON to XML.

In order to achieve this, you can simply create an API like below

<api xmlns="http://ws.apache.org/ns/synapse" name="JsonToXmlApi" context="/Test">
   <resource methods="POST" uri-template="/xml/">
      <inSequence>
         <property name="messageType" value="text/xml" scope="axis2"/>
         <header name="To" action="remove"/>
         <property name="RESPONSE" value="true" scope="default" type="STRING"/>
         <send/>
      </inSequence>
   </resource>
</api>


问:
TF30063: You are not authorized to access Microsoft-IIS/8.5
I got an issue with the tfs.

Firstly after my tfs server database got full.

So in order to solve it i cleared the table of logs from tfs server.

Now at time of clearing data one of my workspace had checkout of one file. As soon i finished my clearing data, it is giving me this error TF30063: You are not authorized to access Microsoft-IIS/8.5. at every time i try to get latest version.

Thats how i am unable to take latest.

Any help would be appritiable.

答:
For me it helps if i just click the Refresh button at the top of the Team Explorer.
After that, the message disappears and i can normally check in.</p>


答:
There are some errors in your code and also wrong if statement I would like to recommend following script to you for sending email.

```<code>$to = $email;
$subject = 'New Order Detail';
$message_text = 'Hi Jane';
$from = 'info@abc.co.in';
$from_name = 'John';
$to_name   = 'Smith';

// Sending email
if(!empty($to) && !empty($message_text ))
{
    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

    // Create email headers
    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

    // Additional headers
    $headers .= 'To: ' . ucwords($to_name) .' <'.$to.'>' . "\r\n";
    $headers .= 'From: ' . ucwords($from_name) . ' <'. $from. '>' ."\r\n";

    // Compose a simple HTML email message
    $message = '<html><body>';
    $message = '<table border="1" align="center" width="50%">';
    $message .= '<tr><td>Test Mail';
    $message .= '</td></tr>';
    $message .= '<p style="color:#080;font-size:18px;">'.$message_text .'</p>';
    $message .= '</table>';
    $message .= '</body></html>';

    // Sending email
    mail($to, $subject, $message, $headers);
    echo 'Your mail has been sent successfully.';
}else{
    echo 'Unable to send email. Please try again.';
}
</code>```

Title: send html template in php

Body:
There are some errors in your code and also wrong if statement I would like to recommend following script to you for sending email.

$to = $email;
$subject = 'New Order Detail';
$message_text = 'Hi Jane';
$from = 'info@abc.co.in';
$from_name = 'John';
$to_name   = 'Smith';

// Sending email
if(!empty($to) && !empty($message_text ))
{
    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

    // Create email headers
    $headers  = 'MIME-Version: 1.0' . "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

    // Additional headers
    $headers .= 'To: ' . ucwords($to_name) .' <'.$to.'>' . "\r\n";
    $headers .= 'From: ' . ucwords($from_name) . ' <'. $from. '>' ."\r\n";

    // Compose a simple HTML email message
    $message = '<html><body>';
    $message = '<table border="1" align="center" width="50%">';
    $message .= '<tr><td>Test Mail';
    $message .= '</td></tr>';
    $message .= '<p style="color:#080;font-size:18px;">'.$message_text .'</p>';
    $message .= '</table>';
    $message .= '</body></html>';

    // Sending email
    mail($to, $subject, $message, $headers);
    echo 'Your mail has been sent successfully.';
}else{
    echo 'Unable to send email. Please try again.';
}


问:
What is the procedure for Minimum contiguous sum with atmost K swaps
I have an array say {-1,2,3,4,-3,-2,1,5}

Now I would like to find the minimum contiguous sum subarray for the given array with atmost K swaps.

In the array above the minimum contiguous sum is -5 and subarray is {-3,-2}

Say for K=1 How should I swap the elements

Should I swap the left element of the sub array i,e; swaping element 4 at a[3]  which is left to it with -1 (again with which number (sub question pops up in my mind)?

a. whether the lowest of the the remaining elements (with any sorting technique of the remaining elements excluding subarrays). If I do this  I will swap -1 with 4 and the min sum will be {-1,-3,-2}
As per "atmost" K swaps I can return this the subarray with only one swap even how long the array is

Should I swap the element 1 at the postion a[6] with -1 and get the sub array with min sum as {-3,-2,-1}. Again following the same question at point a above.

This whole process I would like to do with recursion. As I am dealing with arrays with N integers. Which is best approach I should follow recursion or iteration?

答:
import java.io.;
import java.util.;

class TestClass {

```<code>   static Scanner scanner;
    public static void main(String args[] ) throws Exception {

    scanner=new Scanner(System.in);
    int t=scanner.nextInt();

    while(t>0){
        t--;
    int n=scanner.nextInt();
    int k=scanner.nextInt();
    int[] array=new int[n];
     for(int i=0;i<array.length;i++)
     {
        array[i]=scanner.nextInt();
    }
    int ans=findingMinimumSumSubarray(array,k);
     System.out.println(ans);
    }

}

public static int findingMinimumSumSubarray(int[] values, int k) {
 int len = values.length;
 int res = values[0];
 for (int l = 0; l < len; l++) {
     for (int r = l; r < len; r++) {
         List<Integer> A= new ArrayList<Integer>();
         List<Integer> B = new ArrayList<Integer>();
         int abc = 0;
         for (int i = 0; i < len; i++) {
             if (i >= l && i <= r) {
              A.add(values[i]);
                 abc += values[i];
             } else {
                 B.add(values[i]);
             }
         }

         Collections.sort(A);

         Collections.sort(B);
         Collections.reverse(B);
         res = Math.min(res, abc);
         for (int t = 1; t <= k; t++) {

             if (t > A.size() || t > B.size()) break;

             abc -= A.get(A.size() - t);
             abc += B.get(B.size() - t);
             res = Math.min(res, abc);
         }
     }
 }
 return res;
</code>```

}
}

Body:
import java.io.;
import java.util.;

class TestClass {

   static Scanner scanner;
    public static void main(String args[] ) throws Exception {

    scanner=new Scanner(System.in);
    int t=scanner.nextInt();

    while(t>0){
        t--;
    int n=scanner.nextInt();
    int k=scanner.nextInt();
    int[] array=new int[n];
     for(int i=0;i<array.length;i++)
     {
        array[i]=scanner.nextInt();
    }
    int ans=findingMinimumSumSubarray(array,k);
     System.out.println(ans);
    }

}

public static int findingMinimumSumSubarray(int[] values, int k) {
 int len = values.length;
 int res = values[0];
 for (int l = 0; l < len; l++) {
     for (int r = l; r < len; r++) {
         List<Integer> A= new ArrayList<Integer>();
         List<Integer> B = new ArrayList<Integer>();
         int abc = 0;
         for (int i = 0; i < len; i++) {
             if (i >= l && i <= r) {
              A.add(values[i]);
                 abc += values[i];
             } else {
                 B.add(values[i]);
             }
         }

         Collections.sort(A);

         Collections.sort(B);
         Collections.reverse(B);
         res = Math.min(res, abc);
         for (int t = 1; t <= k; t++) {

             if (t > A.size() || t > B.size()) break;

             abc -= A.get(A.size() - t);
             abc += B.get(B.size() - t);
             res = Math.min(res, abc);
         }
     }
 }
 return res;

}
}


问:
convert month from name to number
Is there an easy way to change $month = "July"; so that $nmonth = 7  (07 would be fine too).
I could do a case statement, but surely there is already a function to convert?
EDIT:
I wish I could accept multiple answers, cause two of you basically gave me what I needed by your powers combined.

```<code>$nmonth = date('m',strtotime($month));
</code>```

That will give the numerical value for $month.
Thanks!

答:
```<code>$dt = '2017-Jan-10';
         OR
$dt = '2017-January-10';
</code>```

echo date('Y-m-d', strtotime($dt));

echo date('Y/m/d', strtotime($dt));

Title: convert month from name to number

Body:
Is there an easy way to change $month = "July"; so that $nmonth = 7  (07 would be fine too).
I could do a case statement, but surely there is already a function to convert?
EDIT:
I wish I could accept multiple answers, cause two of you basically gave me what I needed by your powers combined.

$nmonth = date('m',strtotime($month));

That will give the numerical value for $month.
Thanks!

Body:
$dt = '2017-Jan-10';
         OR
$dt = '2017-January-10';

echo date('Y-m-d', strtotime($dt));

echo date('Y/m/d', strtotime($dt));


问:
How to load CSS and Bootstrap files into server using node.js?
I have a html file called myfile.html that displays 'Hello World'. My css file called myfile.css is used to insert background image. My bootstrap files are used to insert a image in the form of a circle.

The HTML file is as follows:

```<code> <!DOCTYPE html>
 <html>
     <head>
         <title>MY FILE</title>
         <link rel="stylesheet" type="text/css" href="public\css\bootstrap.css">
         <link rel="stylesheet" type="text/css" href="public\myfile.css">
     </head>
     <body>
         <h1>Hello World!!</h1>

         <img src="public\pinky.jpg"  class="img-circle">
     </body>
 </html>
</code>```

My CSS file is as follows:

```<code>body {
    background-image: url('fishy.jpg');
}
</code>```

My node.js file called new.js is as follows:

```<code>  const express = require('express')
  const app = express()
  app.use(express.static('public'))

  app.get('/',function (req,res) {
          console.log(__dirname)
          res.sendFile(__dirname+"/myfile.html")
  })

  app.listen(3000, function() {
          console.log('Example app listening on port 3000!')
  })
</code>```

My main folder is called Bootsstrap and it has the following contents:

```<code>     Bootsstrap
          -myfile.html
          -public /*Folder*/

         /*inside public folder*/
          -myfile.css
          -css
          -js
          -fonts
          -fishy.jpg /*background image*/
          -pinky.jpg /*circular image*/
</code>```

I open Command Prompt from Bootsstrap folder and run

```<code>           node new.js
</code>```

I get the message as:

```<code>          'Example app listening on port 3000!'
</code>```

When I open Chrome Browser and type localhost:3000, I get only 'Hello World'.The images are not getting displayed. I get an Error 404.

What can I do in order to run my HTML file in server using node.js by including all my css and bootstrap files?

答:
If you want to serve static files, such as html files, css, images, you need to make them available for the public. In your existing setup, only myfile.html is available for the public. Since you use css and other files from your server, you need to make them available also. The best way to achieve is to create a public folder and let express to make all the files available in the public folder.

Add to node.js

```<code>   app.use('/', express.static('public'));
</code>```

and rename your myfile.html to index.html

and in your index.html file

```<code><link rel="stylesheet" type="text/css" href="css/bootstrap.css">
 <link rel="stylesheet" type="text/css" href="myfile.css">
</code>```

For example, your node.js should look like something

```<code>  const express = require('express');
  const app = express();
  app.use('/', express.static('public'));

  app.listen(3000, function () {
          console.log('Example app listening on port 3000!');
  });
</code>```

For more info Serving static files in Express

Edit

Your folder structure should be. no need of bootstap folder

```<code>      public //public folder
          -index.html
          -myfile.css
          -css
          -js
          -fonts
          -fishy.jpg
          -pinky.jpg
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I have a html file called myfile.html that displays 'Hello World'. My css file called myfile.css is used to insert background image. My bootstrap files are used to insert a image in the form of a circle.

The HTML file is as follows:

 <!DOCTYPE html>

         <title>MY FILE</title>
         <link rel="stylesheet" type="text/css" href="public\css\bootstrap.css">
         <link rel="stylesheet" type="text/css" href="public\myfile.css">

Hello World!!

My CSS file is as follows:

body {
    background-image: url('fishy.jpg');
}

My node.js file called new.js is as follows:

  const express = require('express')
  const app = express()
  app.use(express.static('public'))

  app.get('/',function (req,res) {
          console.log(__dirname)
          res.sendFile(__dirname+"/myfile.html")
  })

  app.listen(3000, function() {
          console.log('Example app listening on port 3000!')
  })

My main folder is called Bootsstrap and it has the following contents:

     Bootsstrap
          -myfile.html
          -public /*Folder*/

         /*inside public folder*/
          -myfile.css
          -css
          -js
          -fonts
          -fishy.jpg /*background image*/
          -pinky.jpg /*circular image*/

I open Command Prompt from Bootsstrap folder and run

           node new.js

I get the message as:

          'Example app listening on port 3000!'

When I open Chrome Browser and type localhost:3000, I get only 'Hello World'.The images are not getting displayed. I get an Error 404.

What can I do in order to run my HTML file in server using node.js by including all my css and bootstrap files?

Body:
If you want to serve static files, such as html files, css, images, you need to make them available for the public. In your existing setup, only myfile.html is available for the public. Since you use css and other files from your server, you need to make them available also. The best way to achieve is to create a public folder and let express to make all the files available in the public folder.

Add to node.js

   app.use('/', express.static('public'));

and rename your myfile.html to index.html

and in your index.html file

<link rel="stylesheet" type="text/css" href="css/bootstrap.css">
 <link rel="stylesheet" type="text/css" href="myfile.css">

For example, your node.js should look like something

  const express = require('express');
  const app = express();
  app.use('/', express.static('public'));

  app.listen(3000, function () {
          console.log('Example app listening on port 3000!');
  });

For more info Serving static files in Express

Edit

Your folder structure should be. no need of bootstap folder

      public //public folder
          -index.html
          -myfile.css
          -css
          -js
          -fonts
          -fishy.jpg
          -pinky.jpg


问:
How to create a byte[] image intent to share with Email
I am trying to create a byte[] image intent to share with the email app on my Xamarin.Android application. So far I have the following:

```<code>public void ShareAsync(byte[] image, string message)
{
    if (string.IsNullOrEmpty(message) || image == null)
        return;

    var sharingIntent = new Intent();
    sharingIntent.SetAction(Intent.ActionSend);
    sharingIntent.SetType("image/*");
    sharingIntent.PutExtra(Intent.ExtraStream, image);
    sharingIntent.AddFlags(ActivityFlags.GrantReadUriPermission);
    Plugin.CurrentActivity.CrossCurrentActivity.Current.Activity.StartActivity(Intent.CreateChooser(sharingIntent, message));
}
</code>```

This pops up the share dialog and I can click the email app but it returns a blank email.

Is it possible to share a byte[] with the email app so that the image appears as part of the body or an attachment?

答:
```<code>Bitmap bitmap = BitmapFactory.DecodeStream(ContentResolver.OpenInputStream(IMAGE URI));
            using (MemoryStream stream = new MemoryStream())
            {
                bitmap.Compress(Bitmap.CompressFormat.Png, 0, stream);
                translate.bitmapData = stream.ToArray();
            }

            bitmap.Dispose();
</code>```

Body:
I am trying to create a byte[] image intent to share with the email app on my Xamarin.Android application. So far I have the following:

public void ShareAsync(byte[] image, string message)
{
    if (string.IsNullOrEmpty(message) || image == null)
        return;

    var sharingIntent = new Intent();
    sharingIntent.SetAction(Intent.ActionSend);
    sharingIntent.SetType("image/*");
    sharingIntent.PutExtra(Intent.ExtraStream, image);
    sharingIntent.AddFlags(ActivityFlags.GrantReadUriPermission);
    Plugin.CurrentActivity.CrossCurrentActivity.Current.Activity.StartActivity(Intent.CreateChooser(sharingIntent, message));
}

This pops up the share dialog and I can click the email app but it returns a blank email.

Is it possible to share a byte[] with the email app so that the image appears as part of the body or an attachment?


问:
Pandas reindex and fill missing values: "Index must be monotonic"
In answering this stackoverflow question, I found some interesting behavior when using a fill method while reindexing a dataframe.

This old bug report in pandas says that df.reindex(newIndex,method='ffill') should be equivalent to df.reindex(newIndex).ffill(), but that is NOT the behavior I'm witnessing

Here's a code snippet that illustrates the behavior

```<code>df = pd.DataFrame({'values': 2}, index=pd.DatetimeIndex(['2016-06-02', '2016-05-04', '2016-06-03']))
newIndex = pd.DatetimeIndex(['2016-05-04', '2016-06-01', '2016-06-02', '2016-06-03', '2016-06-05'])
print(df.reindex(newIndex).ffill())
print(df.reindex(newIndex, method='ffill'))
</code>```

The first print statement works as expected. The second raises a

```<code>ValueError: index must be monotonic increasing or decreasing
</code>```

What's going on here?

EDIT: Note that the sample df intentionally has a non-monotonic index. The question pertains to the order of operations in df.reindex(newIndex, method='ffil'). My expectation is as the bug-report says it should work- first reindex with the new index and then fill.

As you can see, the newIndex.is_monotonic is True, and the fill works when called separately but fails when called as a parameter to reindex.

答:
It seems that this needs to be done on the columns as well.

```<code>In[76]: frame = DataFrame(np.arange(9).reshape((3, 3)), index=['a', 'c', 'd'],columns=['Ohio', 'Texas', 'California'])

In[77]: frame.reindex(index=['a','b','c','d'],method='ffill',columns=states)
---> ValueError: index must be monotonic increasing or decreasing

In[78]: frame.reindex(index=['a','b','c','d'],method='ffill',columns=states.sort())

Out[78]:
  Ohio  Texas  California
a     0      1           2
b     0      1           2
c     3      4           5
d     6      7           8
</code>```

Body:
In answering this stackoverflow question, I found some interesting behavior when using a fill method while reindexing a dataframe.

This old bug report in pandas says that df.reindex(newIndex,method='ffill') should be equivalent to df.reindex(newIndex).ffill(), but that is NOT the behavior I'm witnessing

Here's a code snippet that illustrates the behavior

df = pd.DataFrame({'values': 2}, index=pd.DatetimeIndex(['2016-06-02', '2016-05-04', '2016-06-03']))
newIndex = pd.DatetimeIndex(['2016-05-04', '2016-06-01', '2016-06-02', '2016-06-03', '2016-06-05'])
print(df.reindex(newIndex).ffill())
print(df.reindex(newIndex, method='ffill'))

The first print statement works as expected. The second raises a

ValueError: index must be monotonic increasing or decreasing

What's going on here?

EDIT: Note that the sample df intentionally has a non-monotonic index. The question pertains to the order of operations in df.reindex(newIndex, method='ffil'). My expectation is as the bug-report says it should work- first reindex with the new index and then fill.

As you can see, the newIndex.is_monotonic is True, and the fill works when called separately but fails when called as a parameter to reindex.

Body:
It seems that this needs to be done on the columns as well.

In[76]: frame = DataFrame(np.arange(9).reshape((3, 3)), index=['a', 'c', 'd'],columns=['Ohio', 'Texas', 'California'])

In[77]: frame.reindex(index=['a','b','c','d'],method='ffill',columns=states)
---> ValueError: index must be monotonic increasing or decreasing

In[78]: frame.reindex(index=['a','b','c','d'],method='ffill',columns=states.sort())

Out[78]:
  Ohio  Texas  California
a     0      1           2
b     0      1           2
c     3      4           5
d     6      7           8


问:
How to run exe before or after "RunProgram" from 7zip sfx files setting on config.txt?
I am using 7zip to pack an installation (im using 7z because i need it to work in linux and windows).

From what i've been reading the config.txt file can contains this:

```<code>;!@Install@!UTF-8!
RunProgram="Installer.exe"
;!@InstallEnd@!
</code>```

But what if i need to run another exe before or after running installer.exe? Is it possible like it is in NSIS?

答: You can specify another progrem in 'RUnProgram' config parameter. Lets say its some .bat file. At the moment program is run, files are already extracted at TEMP location. As your .bat will run then, it can call Installer.exe once script is finished preparations.</p>

Body:
I am using 7zip to pack an installation (im using 7z because i need it to work in linux and windows).

From what i've been reading the config.txt file can contains this:

;!@Install@!UTF-8!
RunProgram="Installer.exe"
;!@InstallEnd@!

But what if i need to run another exe before or after running installer.exe? Is it possible like it is in NSIS?