Replace text between specific words in sql server

I got column with nvarchar(max) type. it has html text as string. like bellow.

<a>hi_this </a>
<i>is_sample</i>
<p>text_data</P>
<a>in_column</a>
<p>this_is_paragraph</p>
<a>this_is_end</a>

Is there anyway top replace underscore( _ ) symbol between tags <p> and </p> with space

so I want final output like bellow.

<a>hi_this </a>
<i>is_sample</i>
<p>text data</P>
<a>in_column</a>
<p>this is paragraph</p>
<a>this_is_end</a>

is it possible?

Update: it is in single row like this. '< a >hi_this < /a >< i >is_sample< /i >< p >text data< /P >< a >in_column< /a > < p >this is paragraph< /p > < a >this_is_end< /a >'

Answers


I really don't think SQL is the best tool for this, but I have done something that seems to work for your situation. The first step is to build a table of numbers. If you already have one then great, use that, but for the sake of a complete answer I have assumed you don't.

This is done by simply using a table value constructor to create a table of 10 rows (N1), then cross joining this with itself to get 100 rows (N3), then cross joining this with itself to get a table of 10,000 rows (Numbers) then using ROW_NUMBER() to get a number from 1-10,000 for each row:

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)

SELECT  *
FROM    Numbers;

Further reading on this, and other methods of doing this can be found in the series Generate a set or sequence without loops. If you need more than 10,000 numbers simply add more cross joins until you have enough

Once you have your numbers table, you can use SUBSTRING() to identify the position of all of your opening <p> tags, using `

DECLARE @S NVARCHAR(500) = '
    <a>hi_this </a>
    <i>is_sample</i>
    <p>text_data</P>
    <a>in_column</a>
    <p>this_is_paragraph</p>
    <a>this_is_end</a>';

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)

SELECT  OriginalString = SUBSTRING(@S, Number, CHARINDEX('</p>', @s, Number + 1) - Number + 4),
        Start = Number,
        NumChars = CHARINDEX('</p>', @s, Number + 1) - Number + 4,
        NewString = REPLACE(SUBSTRING(@S, Number, CHARINDEX('</p>', @s, Number + 1) - Number + 4), '_', ' ')
FROM    Numbers
WHERE   SUBSTRING(@S, Number, 3) = '<p>';

Result:

OriginalString              Start       NumChars    NewString
---------------------------------------------------------------------------
<p>text_data</P>            40          16          <p>text data</P>
<p>this_is_paragraph</p>    80          24          <p>this is paragraph</p>

Here you use SUBSTRING(@S, Number, 3) = '<p>' to get the starting position of each p tag, then you can use CHARINDEX() to get the position of the next closing p tag, and replace the text in between.

Finally you need to use the output from this to replace the original string, which you can do using STUFF():

DECLARE @S NVARCHAR(500) = '
    <a>hi_this </a>
    <i>is_sample</i>
    <p>text_data</P>
    <a>in_column</a>
    <p>this_is_paragraph</p>
    <a>this_is_end</a>';

WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Data AS
(   SELECT  OriginalString = SUBSTRING(@S, Number, CHARINDEX('</p>', @s, Number + 1) - Number + 4),
            Start = Number,
            NumChars = CHARINDEX('</p>', @s, Number + 1) - Number + 4,
            NewString = REPLACE(SUBSTRING(@S, Number, CHARINDEX('</p>', @s, Number + 1) - Number + 4), '_', ' ')
    FROM    Numbers
    WHERE   SUBSTRING(@S, Number, 3) = '<p>'
)
SELECT  @S = STUFF(@S, Start, NumChars, NewString)
FROM    Data;

PRINT @S;

Which gives:

    <a>hi_this </a>
    <i>is_sample</i>
    <p>text data</P>
    <a>in_column</a>
    <p>this is paragraph</p>
    <a>this_is_end</a>

Why not use a simple REPLACE funtion

update my_table
set path = replace(path, '_', ' ')
WHERE path like '<p>%'

Need Your Help

Published a ClickOnce application and it keeps resetting its settings

c# visual-studio-2010 settings clickonce publish

I have deployed this application using Visual Studio 2010's Publish feature; it needs to save a few user settings (such as database connection information) and there is this particular computer that

Linker errors LNK1120 and LNK2001

c++ visual-c++ linker-error visual-c++-2005

I hate these linker errors, any idea how I can get rid of them?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.