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!

Yes a sql function could possibly do that. If you want more details you need to provide some to us first so we know what you are really trying to do. — Sean Lange, Aug 08 '16 at 21:32
Why don't you make an effort to write a stored procedure to do what you want? — Missy, Aug 08 '16 at 21:34
this can be done with row_number() and a cte or nested query fairly easily. My question is how will you break the 1,300 rows to know when to all the
head
multiple times? Can we assume you can read the csv to a temp table or to open a rowset to it? — Matt, Aug 08 '16 at 21:41
There is nothing magical about HTML. Just concatenate strings. You really need to explain further what you're after though — Nick.Mc, Aug 08 '16 at 22:08
You have a text file, and you want to process it and output an HTML file? That has nothing to do with a database. SQL Server is not the right tool for this job. — Blorgbeard, Aug 08 '16 at 22:13

score 0 · Answer 1 · answered Aug 08 '16 at 22:13

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

SQL Server Function that will return a text in HTML format

1 Answers1