SQL function for returning characters from a string after the maxvalue and adding tag before and after the string

userEqCl1 0
Viewed 35
CREATE FUNCTION [dbo].[AppendSuffix]
(
    @P_Input NVARCHAR(50),
    @P_MaxValue int
)
RETURNS NVARCHAR(55)
AS
BEGIN
    DECLARE @V_Result NVARCHAR(MAX);
    DECLARE @V_ERROR NVARCHAR(MAX);
    //i need to put logic here
    
END;

Example: if I pass in input="value", minvalue = 1, maxvalue = 2

then the resulting output shall be

<ag>lue<ag>
sql-server sql
1 Answers

If the tags are fixed, and you "only" needs the @P_MaxValue, then you could use something like this:

CREATE FUNCTION [dbo].[AppendSuffix]
    (@P_Input NVARCHAR(50),
     @P_MaxValue int)
RETURNS NVARCHAR(55)
AS
BEGIN
    DECLARE @V_Result NVARCHAR(MAX);
    DECLARE @V_ERROR NVARCHAR(MAX);

    -- check if we have a string that's even long enough to get trimmed
    -- if not - just return the input string 
    -- this might need to be adapted to your needs!
    IF (LEN(@P_Input) <= @P_MaxValue)
        RETURN @P_Input;

    -- if input string *is* long enough
    -- (1) add a <ag> tag in the beginning
    -- (2) get substring of input string, from MaxValue onwards
    -- (3) add another <ag> tag at the end
    RETURN '<ag>' + 
           SUBSTRING(@P_Input, @P_MaxValue + 1, LEN(@P_Input) - @P_MaxValue) + 
           '<ag>';
END;

Executions:

SELECT dbo.AppendSuffix('value', 2);

--> returns <ag>lue<ag>

SELECT dbo.AppendSuffix('value', 25);

--> returns value

SELECT dbo.AppendSuffix('this is a rather long string', 12);

--> returns <ag>ther long string<ag>

marc_s0
Related