0

I have a temp table with a column titled AdditionalData and for e.g. each cell contains a string (of varying length) similar to this:

<AdditionalData><Field22101>Zachary, LA</Field22101><NetMiles>23</NetMiles><MileageRate>.585</MileageRate><Field44444>Customer Related - 04</Field44444></AdditionalData>

I would like to replace everything in angle brackets by an empty space. So the output should be something like this:

Zachary, LA 23 .585 Customer Related - 04

This is my first attempt:

UPDATE TempTable

SET tAddlData = STUFF(AdditionalData, PATINDEX('<%>', AdditionalData), CHARINDEX('>', AdditionalData), '')

Obviously this only takes care of the first set of angle brackets. I know I have to use a WHILE LOOP in there but unsure how to proceed further. Can you please advise?

Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
Kaizen
  • 35
  • 1
  • 6
  • What does `SELECT @@version;` report? I think you are using Microsoft SQL Server, not MySQL, but you have tagged the question [tag:mysql]. PATINDEX() is not a [builtin function supported by MySQL](https://dev.mysql.com/doc/refman/en/built-in-function-reference.html). Nor is STUFF(). But they are both built-in string functions of Microsoft: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver16 – Bill Karwin Aug 30 '22 at 20:22
  • My apologies for the erroneous tag - edited to correct. SQL Server 2016. – Kaizen Aug 30 '22 at 20:30
  • 2
    Use [tag:sql-server] for the Microsoft product. The tag [tag:sql] is used for the SQL language more generally. See the descriptions as you click on those tags. – Bill Karwin Aug 30 '22 at 20:31
  • @Barmar have you got a dup for SQL Server since the tags have changed? – Dale K Aug 30 '22 at 20:35
  • No, but I think the solutions there using `REGEXP_REPLACE()` should work. – Barmar Aug 30 '22 at 20:36
  • 1
    @Barmar not in SQL Server - there is no `REGEXP_REPLACE` function. – Dale K Aug 30 '22 at 20:37
  • Sorry, I assumed MySQL was the only DBMS that took so longer to get `REGEXP_REPLACE`. Maybe this: https://stackoverflow.com/questions/61488458/what-is-regexp-replace-equivalent-in-sql-server – Barmar Aug 30 '22 at 20:38
  • 1
    I think if you search for the **actual** problem i.e. to replace xml tags or similar you'll find exactly the solution you want, maybe this one https://stackoverflow.com/questions/38868364/sql-remove-all-html-tags-in-a-string – Dale K Aug 30 '22 at 20:42
  • Thanks for cleaning up my post. So the Field Number substring can vary from row to row, so that's why I thought my approach should be to target anything between angle brackets. The other post seems to be more geared towards standardized "html tags" like – Kaizen Aug 30 '22 at 20:48
  • 1
    @Kaizen you can use the technique, html tags are just a more specific example of what you are trying to do. Just tweak the methods they use. The accepted answer only looks for angle brackets. – Dale K Aug 30 '22 at 20:55
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Aug 30 '22 at 21:08

2 Answers2

1

A minimal reproducible example is not provided. So, I am shooting from the hip.

I am assuming that the AdditionalData column has XML data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, AdditionalData XML);
INSERT @tbl (AdditionalData) VALUES
(N'<AdditionalData>
    <Field22101>Zachary, LA</Field22101>
    <NetMiles>23</NetMiles>
    <MileageRate>.585</MileageRate>
    <Field44444>Customer Related - 04</Field44444>
</AdditionalData>');
-- DDL and sample data population, end

SELECT ID 
, result = AdditionalData.query('data(/*/*/text())').value('text()[1]', 'VARCHAR(MAX)')
FROM @tbl;

Output

ID result
1 Zachary, LA 23 .585 Customer Related - 04
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
0

The other post SQL - Remove all HTML tags in a string linked by Dale K actually solved my problem. Thank you.

Solution:

UPDATE TempTable

SET tAddlData = CAST(AdditionalData AS XML).value('.', 'nvarchar(max)')

Kaizen
  • 35
  • 1
  • 6