SQL Server SQL Query to Get Values between characters in string

epfja78i 于 2023-10-15 发布在其他

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

I have a SQL field that stores HTML such as this:

Table: Template
Fields: NotificationId (int), Template (varchar(max))

Example row: NotificationId 1, Template The template field contains HTML in this form:

<body>
<table border="1">
<tr><td>{RESULTFLD:0:customer_name}</td></tr>
<tr><td>{RESULTFLD:1:customer_address1}</td></tr>
<tr><td>{RESULTFLD:2:customer_address2}</td></tr>
</body>
</table>

What I need to do is parse this somehow where I want to get if possible the NotificationId then a list of each field name in the HTML. For example:

NotificationId 1, then each field name with a line break if possible customer_name customer_address1 customer_address2

Tricky part seems to be the number of the result field increments so trying to parse this HTML to get those values.

Thanks for any help if this seems doable.

sql-server

来源：https://stackoverflow.com/questions/77259739/sql-query-to-get-values-between-characters-in-string

1条答案

按热度按时间

yqlxgs2m1#

Here is a query which should achieve your requirement. As noted in the comments, however, just because you can it does not mean you should. Doing this in SQL is always going to be hacky.

CREATE TABLE htmldata ( NotificationId int, Template varchar(max))

INSERT INTO htmldata VALUES (1, '<body>
<table border="1">
<tr><td>{RESULTFLD:0:customer_name}</td></tr>
<tr><td>{RESULTFLD:1:customer_address1}</td></tr>
<tr><td>{RESULTFLD:2:customer_address2}</td></tr>
</body>
</table>')

DECLARE @NotificationId INT;
DECLARE @Template NVARCHAR(MAX);
DECLARE @StartPosition INT;
DECLARE @EndPosition INT;
DECLARE @FieldName NVARCHAR(MAX);
DECLARE @ResultTable TABLE (NotificationId INT, FieldName NVARCHAR(MAX));

-- Create a cursor to iterate through the rows
DECLARE template_cursor CURSOR FOR
SELECT NotificationId, Template
FROM htmldata;

OPEN template_cursor;

FETCH NEXT FROM template_cursor INTO @NotificationId, @Template;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @StartPosition = CHARINDEX('{RESULTFLD:0:', @Template);
    
    WHILE @StartPosition > 0
    BEGIN
        SET @EndPosition = CHARINDEX('}', @Template, @StartPosition);
        
        IF @EndPosition > 0
        BEGIN
            -- Extract the field name between {RESULTFLD:<n>: and }
            SET @FieldName = SUBSTRING(@Template, @StartPosition + 13, @EndPosition - @StartPosition - 13) + ' ';
            
            -- Insert the result into the result table
            INSERT INTO @ResultTable (NotificationId, FieldName)
            VALUES (@NotificationId, @FieldName);
            
            -- Move to the next occurrence
            SET @StartPosition = CHARINDEX('{RESULTFLD:', @Template, @EndPosition);
        END
        ELSE
        BEGIN
            -- If no closing '}' found, exit the loop
            BREAK;
        END
    END
    
    FETCH NEXT FROM template_cursor INTO @NotificationId, @Template;
END

CLOSE template_cursor;
DEALLOCATE template_cursor;

-- Retrieve the results
SELECT CONCAT(NotificationId, ' ', STRING_AGG(FieldName, '')) AS NotificationAndFieldNames
FROM @ResultTable
GROUP BY NotificationId;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL Query to Get Values between characters in string

1条答案

相关问题

热门标签

最新问答