SQL Server How to limit text characters in column while still allowing unlimited HTML styles?

fxnxkyjh 于 2024-01-05 发布在其他

关注(0)|答案(1)|浏览(118)

If I have a column for notes/comments in a table, what is an appropriate way / is there a way to store a limited number of characters (say 600) while still allowing for unlimited HTML style markup?

The only way I could think of is to enforce the limit at the backend but I'm concerned it might not be enough.

sql-server

来源：https://stackoverflow.com/questions/77675083/how-to-limit-text-characters-in-column-while-still-allowing-unlimited-html-style

1条答案

按热度按时间

u3r8eeie1#

If your HTML is really (X)HTML, it is possible to use SQL Server XML data type and built-in XQuery language capabilities.

XQuery allows to retrieve textual data only excluding HTML tags.

Even embedded CSS <style>...</style> could be easily taken out of the equation.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, notes XML);
INSERT @tbl (notes) VALUES
(N'<html>
  <head>
    <meta charset="utf-8" />
    <style>
        table  {border-collapse: collapse; } 
        th  {background-color: #4CAF50; color: white;} 
        th, td  { text-align: center; padding: 8px;} 
        tr.yellow td  {background-color: yellow;} 
    </style>
  </head>
  <body>
    <table border="1" cellpadding="10">
      <thead>
        <tr>
          <th>No.</th>
          <th>State</th>
          <th>City</th>
        </tr>
      </thead>
      <tbody>
        <tr align="center" valign="center" class="yellow">
          <td>1</td>
          <td>FL</td>
          <td>Miami</td>
        </tr>
        <tr align="center" valign="center">
          <td>2</td>
          <td>FL</td>
          <td>Orlando</td>
        </tr>
        <tr align="center" valign="center">
          <td>3</td>
          <td>TX</td>
          <td>Dallas</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>');
-- DDL and sample data population, end
SELECT t.*
    , textOnly, LEN(textOnly) AS LenTextOnly
FROM @tbl AS t
CROSS APPLY (SELECT notes.query('
    for $x in /html//*[local-name()!="style"]/text()
    return $x
    ').value('text()[1]', 'NVARCHAR(MAX)')) AS t1(textOnly);

Output

ID	textOnly	LenTextOnly
1	No.StateCity1FLMiami2FLOrlando3TXDallas	39

展开查看全部

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server How to limit text characters in column while still allowing unlimited HTML styles?

1条答案

相关问题

热门标签

最新问答