MSSQL Server Check if a line is too long in a string field

Imagine this: 

You want to make sure lines aren't longer than x characters to avoid automatic line breaks when printing from an sql database table to a form. We can warn users by using raiseerror.

DECLARE @xml XML

declare @input varchar(max) = :positionstext

SET @xml  = CAST('<col>' + REPLACE(REPLACE(@input, CHAR(10), ''),CHAR(13),'</col><col>') + '</col>' AS XML)

 

-- create a cursor for looping

DECLARE @col VARCHAR(max)

DECLARE @i INT = 0;

 

declare cur cursor local fast_forward for

    select

        m.c.value('(text())[1]', 'nvarchar(max)') as col

    from @xml.nodes('/col') as m(c)

open cur

while 1 = 1

begin

    fetch cur into @col

    if @@fetch_status <> 0 break

    SET @i += 1;

   

    DECLARE @warning VARCHAR(max) =  'Warning. Please check line ' + cast(@i as varchar)

          

   IF(LEN(@col) > 85) -- change number of characters here

     RAISERROR (@warning, 16, 0);

 

end

close cur

deallocate cur

 

-- This is here to avoid an error

 select

        m.c.value('(text())[1]', 'nvarchar(max)') as col

    from @xml.nodes('/col') as m(c)

Written by Andreas Wildi on Tuesday April 14, 2015
Permalink -

« Update Bolt CMS from 1.0 to 2.0 - How to create an information display »