liujchen 26197 (4/16/2013)

---

I have written another UDF to use while loop shown as below:

create function dbo.[ReplaceSplVarcharsBICD_test3] (@s varchar(8000)) returns varchar(8000)

with schemabinding

begin

if @s-2 is null

return null

declare @s2 varchar(8000)

set @s2 = ''

declare @l int

set @l = len(@s)

declare @p int

set @p = 1

while @p <= @l begin

declare @C int

set @C = ascii(substring(@s, @p, 1))

--any characters less than 32(dec,Space), or 124 (PIPE) or 127 (DEL) need to be replace with 32 (dec,Space)

if @C between 32 and 123 or @C between 125 and 126

set @s2 = @s2 + char(@c)

--any characters less than 32(dec,Space), or 124 (PIPE) or 127 (DEL) need to be replace with 32 (dec,Space)

if @C between 0 and 31 or @C = 124 or @C= 127

set @s2 = @s2 + char(32)

set @p = @p + 1

end

if len(@s2) = 0

return null

return ltrim(rtrim(@s2))

end

go

Testing results:

1. If we don't call any udf, it took 30 minutes to dump all the data into a file

2. If we use the first udf, it took 2.5 hours to dump all the data into a file

3. If we use the second udf, it took 35 minutes to dump all the data into a file.

Can you let me know if I rewrite the UDF into inline table level UDF, how to use the cross apply

My current sql is shown as below:

SELECT 'D' AS RECORDTYPECODE

, a.[Id]

,a.[FundsMovementItemTypeId]

,a.[FundsMovementSetId]

,a.[FeeTypeId]

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountName])

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[GLAccountName])

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[LodgementReference])

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountNumber])

,a.[IsSundryParties]

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceBSB])

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceAccountName])

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[TraceAccountNumber])

,a.[IsSundryPartiesTraceACC]

,a.[IsAttractGST]

,a.[IsCustomerAccount]

,a.[Amount]

,a.[OnDateTime]

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[AccountBankCode])

,a.[U1]

,%DBNAME%.ReplaceSplVarcharsBICD_test3(a.[U2])

,a.[U3]

,a.[U5]

,a.[genID]

FROM %DBNAME%.FundsMovementItem a