CODEWITHSUNDEEP
t-sql
user484002
user484002

Reputation:

Performance issue in a T-SQL function

I've written the following T-SQL function, but on implementation it seems to be very slow (it basically functions to return a phone number from a string such as "01530 999111 - don't phone in the evening!".

Do any of you marvellously insightful people have any tips for me in order to improve the performance of this function or have any more efficient alternatives to suggest?

ALTER FUNCTION [dbo].[xfnJustNumbers](@inStr varchar(255))
RETURNS [varchar](255)
AS
BEGIN

  DECLARE @outStr varchar(255)
  SELECT @outStr = ''
  DECLARE @charNo int
  SELECT @charNo = 0
  WHILE @CharNo < len(@inStr) begin
    SELECT @CharNo = @CharNo +1
    IF isnumeric(substring(@inStr,@CharNo,1))=1 SELECT @outStr = @outStr + substring(@inStr,@CharNo,1)
  END
  RETURN @outStr

END

Thanks :)

Upvotes: 1

Views: 487

Answers (1)

Martin Smith
Martin Smith

Reputation: 453037

You could probably eke out a bit of additional performance from the TSQL version by trying some of the following.

  1. Only calling it if you establish the string does contain at least one number CASE WHEN COl NOT LIKE '%[0-9]%' THEN '' ELSE [dbo].[xfnJustNumbers](col) END
  2. Storing substring(@inStr,@CharNo,1) and len(@inStr) in variables rather than evaluating them more than once.
  3. Checking ASCII(@chr) rather than calling isnumeric this is likely to be more what you need anyway.
  4. Including the WITH SCHEMABINDING option in the function declaration to ensure that plans using these UDFs do not do unnecessary spools.

Having said all that though I'd use a CLR function using RegularExpressions for this.

Upvotes: 1

Related Questions