CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2sql-server-2012
anvesh.d
anvesh.d

Reputation: 118

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 >'

Upvotes: 1

Views: 1468

Answers (3)

GarethD
GarethD

Reputation: 69769

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>

Upvotes: 2

d_luffy_de
d_luffy_de

Reputation: 967

Why not use a simple REPLACE funtion

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

Upvotes: 2

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can use REPLACE with CASE in following:

SELECT CASE 
          WHEN col LIKE '<p>%' AND col LIKE '%</p>' THEN REPLACE(col, '_', ' ') 
          ELSE col 
       END AS col
FROM #table1

OUTPUT

<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>

SQL FIDDLE

UPDATE

As you want, this will combine result set to 1 row:

DECLARE @combine VARCHAR(MAX)

;WITH cte AS (
   SELECT CASE WHEN col LIKE '<p>%' and col LIKE '%</p>' THEN REPLACE(col, '_', ' ') ELSE col END AS col
   FROM table1
)
SELECT @combine = COALESCE(@combine, ' ') + col
FROM cte
SELECT @combine as col

OUTPUT

 <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>

SQL FIDDLE

Upvotes: 0

Related Questions