Search Code Shode

Tuesday, February 11, 2014

MS SQL Server Function to Insert Line Break in String

Today I came across a situation where I had to break a string coming from SQL and show it in different lines on HTML. I know it's a weird situation, but so is programming :)

I googled for it but couldn't find an exact solution so I decided to write an SQL user-defined function myself.
While writing a function a thought why only limit it to a line-break (<br/>) So i made it more generic.

here is the function:
CREATE FUNCTION [dbo].Stringlinebreak(@String NVARCHAR(1000), 
                                      @Length INT, 
                                      @Char   VARCHAR(5)) 
returns NVARCHAR(max) 
      DECLARE @ReturnString NVARCHAR(max) 
      DECLARE @ActualLength INT 

      SET @ReturnString = @String 

      SELECT @ActualLength = Len(@String) 

      IF( @ActualLength > ( @Length * 2 ) ) 
            SET @I = @Length 

            WHILE @I <= @ActualLength 
                  SELECT @ReturnString = Stuff(@ReturnString, @I - 5, 1, 
                                         Substring(@ReturnString, @I-5, 1 
                                                ) + @Char) 

                  SET @I = @I + @Length 
            SELECT @ReturnString = CASE 
                                     WHEN @ActualLength > @Length THEN 
                                     Stuff(@String, @Length - 5, 1, 
                                     Substring(@String, @Length-5, 1) + @Char) 
                                     ELSE @String 

      RETURN @ReturnString 

it can be used as:

SELECT [dbo].Stringlinebreak('Microsoft Certified Database Administrator on Microsoft SQL Server 2000', 30, '<br/>') 

and the output will be:
Microsoft Certified Datab<br/>ase Administrator on Micr<br/>osoft SQL Server 2000

So when it is displayed in HTML, the string is with line breaks.

You can try this code, here at SQL Fiddle

I hope it helps someone else too.

Happy Coding!