Tune a SQL scalar function that does simple operations a lot of times

Question

I have a column of data type image those values look similar to this:

0x...32004200460054004F00560031004800360053005100380031006500300043004300550055003500350034003300370038005600420047003400310047004F004A00460030004C003100370030005200380054003600370045004F00320032004E005600360039004C00...

I have to use only a certain sequence of the image value. And I need to convert it to a character like data type (VARCHAR?) like this:

2BFTOV1H6SQ81e0CCUU554378VBG41GOJF0L170R8T67EO22NV69L

The convertion is done as follows:

Ommit every second pair (it's always 00) What remains represents the ASCII codes of the desired result (32 -> 2, 42 -> B, ...).

I need to tune this because I have to apply it millions of millions of times. Right now I make 1 mio. rows in 3 minutes.

My current attempt is this SQL scalar function:

ALTER FUNCTION [dbo].[F_Get_CClip](@pi_content_referral_blob IMAGE)
RETURNS VARCHAR(53)
AS

BEGIN
DECLARE @l_content_referral_blob VARCHAR(107),
        @l_position INTEGER,
        @l_text_ascii VARCHAR(53)

IF NOT @pi_content_referral_blob IS NULL AND LEN(CONVERT(VARBINARY(MAX), @pi_content_referral_blob)) > 187
BEGIN
    SET @l_content_referral_blob = SUBSTRING(CONVERT(VARCHAR(188), CONVERT(VARBINARY(188), @pi_content_referral_blob)), 29, 105)
    SET @l_position = 1
    SET @l_text_ascii = '' 

    WHILE @l_position < LEN(@l_content_referral_blob) + 1
    BEGIN
        SET @l_text_ascii = @l_text_ascii + SUBSTRING(@l_content_referral_blob, @l_position, 1)
        SET @l_position = @l_position + 2
    END
END
ELSE IF @pi_content_referral_blob IS NULL
    SET @l_text_ascii = NULL
ELSE
    SET @l_text_ascii = ''

RETURN @l_text_ascii
END

Answer 1

This entire function is pointless. You can just cast in a number of steps: image -> varbinary(max) -> nvarchar(max) -> varchar(max)

SELECT
  CAST(
    CAST(
      CAST(
        YourImageColumn
        AS varbinary(max)
      )
      AS nvarchar(max)
    )
    AS varchar(max)
  )
FROM ...

db<>fiddle

Note that the image datatype was deprecated many years ago, and you can safely convert all columns to varbinary(max).

---

If you really really wanted to use a function here, you could turn it into a Table Valued Function, and use GENERATE_SERIES and SUBSTRING to break out the characters.

CREATE OR ALTER FUNCTION dbo.ConvertToVarchar(@value varbinary(max))
RETURNS TABLE
AS RETURN

SELECT
  Result =
    STRING_AGG(
      CAST(SUBSTRING(@value, n.value, CAST(1 AS bigint)) AS varchar(max))
      , '') WITHIN GROUP (ORDER BY n.value)
FROM GENERATE_SERIES(CAST(1 AS bigint), LEN(@value) - 1, CAST(2 AS bigint)) n;