SQL Server – Adding End-of-Line Markers to a String

sql serversql-server-2008

We have Scenario where we need to modify input string while fetching data from SQL Server.

"TEST RATE CODE DESC TEST RATE CODE DESCTST TEST" and C is 47th character in an "DESCTST" then push delimiter at the beginning of a word. like "\DESCTST"

For Example:

Current => TEST RATE CODE DESC TEST RATE CODE DESCTST TEST\

Required =>
TEST RATE CODE DESC TEST RATE CODE DESCTST \TEST

We have created an SQL function for achieving this and everything working fine except the backward operation as mentioned in the example.

Here is my SQL function:

ALTER  FUNCTION [dbo].[DescFormatter_New]
(
    @InputString nvarchar(max), 
    @MaxLength int=0,
    @CharPerLine int=0,
    @NoOfLines int=0
)
RETURNS nvarchar(max)
AS
BEGIN   
    DECLARE @ResultText nvarchar(max)
    DECLARE @I INT
    DECLARE @COUNT INT

    IF(LEN(@InputString)<@CharPerLine)
        BEGIN
            SET @ResultText=@InputString
        END
    ELSE
        BEGIN
            set @InputString=LEFT(@InputString,@MaxLength)                              
            SET @I = LEN(@InputString)/@CharPerLine
            
            if((@I*@CharPerLine)<LEN(@InputString))
                BEGIN
                    SET @I=@I+1 
                END         

            SET @COUNT =1
            SET @ResultText= LEFT((@InputString),@CharPerLine)
            if(@I>@NoOfLines)
                Begin
                    set @I=@NoOfLines
                End
            WHILE(@I>1 AND @I<=@NoOfLines)
                BEGIN
                    SET @ResultText=@ResultText+'\'+SUBSTRING(@InputString,(@COUNT*@CharPerLine)+1,@CharPerLine)
                    SET @COUNT=@COUNT+1
                    SET @I=@I-1
                END
        END
    RETURN @ResultText
END

We call this function like here:

[dbo].[DescFormatter_New](LText,94,47,2) AS [Short Description]

Note that we will need multiple line delimiters for the string which contains more than 100 or 200 characters.

here are the few examples :

1)
Input String = Deluxe and spacious 1 King Bed NS in room Whirlpool
Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor

Expected Output = Deluxe and spacious 1 King Bed NS in room \Whirlpool
Computer Wet Bar Refrigerator Mini \Bar Desk Safe WIFI Warm Decor

2)
Input String = Spacious 1 Queen Bed Non Smoking featuring flat screen TV with cable wifi coffeemaker mini frig microwave hairdryer iron with board test test

Expected Output = Spacious 1 Queen Bed Non Smoking featuring flat\ screen TV with cable wifi coffeemaker mini \frig microwave hairdryer iron with board test \test

In the above expected output string the first deliminator come after 47 charecters i.e between Whirl\pool (diaplayed as \Whirlpool) and then the loop will start from count 1 again after Whirl in the Whirlpool, So the second deliminator should come like here Des\k (Displayed as \Desk).

Best Answer

How do they do it

Fisrt step, select first substring of N characters in reverse mode.

REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition))<

Second one, find first space position in the reversed string, that is the beginning of the selected word:

SELECT @pos = CHARINDEX(' ', @Str);

Then reverse it again, get left part of the string and add a separator '/':

LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';

Last step by calculating next initial position, according to the beginning of the selected word:

@InitPos + @CharPosition - @Pos + 1;

The whole function:

CREATE FUNCTION [DescFormatter] (@Text nvarchar(max), @CharPosition int)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Pos int = 0;
    DECLARE @Str nvarchar(max) = '';
    DECLARE @Out nvarchar(max) = '';
    DECLARE @InitPos int = 1;

    WHILE @InitPos + @CharPosition < LEN(@Text)
    BEGIN
        SELECT @Str = REVERSE(SUBSTRING(@Text, @InitPos, @CharPosition));
        SELECT @Pos = CHARINDEX(' ', @Str);
        SELECT @Out = @Out + LEFT(REVERSE(@Str), @CharPosition - @Pos + 1) + '/';
        SET @InitPos = @InitPos + @CharPosition - @Pos + 1;
    END

    SELECT @Out = @Out + SUBSTRING(@Text, @InitPos, LEN(@Text) - @InitPos + 1);

    RETURN @Out;    
END
GO

How to use it:

SELECT dbo.[DescFormatter]('Deluxe and spacious 1 King Bed NS in room Whirlpool Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor', 47);

This is the result:

Deluxe and spacious 1 King Bed NS in room /Whirlpool Computer Wet Bar Refrigerator Mini /Bar Desk Safe WIFI Warm Decor

dbfiddle here

Related Question