SQL Server Function that will return a text in HTML format

Question

I have a csv file that contains resume in plain text. I need to convert the text field from a text to html code.

For example:

Plain text:

Head

Line2
Line3
Line4

Converted:

<p>Head</p>

<p>Line2<br />
Line3<br />
Line4</p>

Can a SQL Server function can do this? I already saw a online tool http://www.textfixer.com/html/convert-text-html.php that can do similar function, but I need to convert at least 1300 rows of resume that inside a csv file.

Thanks in advance!

Answer 1

I will assume you can put the csv into a temp table or even just write this against the csv using open rowset or something but here is an idea of how to get the encoding you want. If more than one resume exists replace PARTITON BY 1 with PARTITION BY UniqueResumeId

;WITH cteOriginalRowNumber AS (
    SELECT
       Line
       ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY (SELECT 0)) as OriginalRowNum
    FROM
       CSVSource
)

, cteReverseRowNumber AS (
    SELECT
       Line
       ,OriginalRowNum
       ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY OriginalRowNum DESC) as ReverseRowNum
    FROM
       cteOriginalRowNumber
)

SELECT
    CASE
       WHEN
          OriginalRowNum = 1
          OR (OriginalRowNum = 2 AND ReverseRowNum = 1)
          THEN '<p>' + Line + '</p>'
       WHEN OriginalRowNum = 2 THEN '<p>' + Line + '<br />'
       WHEN OriginalRowNum > 2 AND ReverseRowNum = 1 THEN Line + '</p>'
       ELSE Line + '<br />'
    END
FROM
    cteReverseRowNumber
ORDER BY
    OriginalRowNum