Solved

deleting specific string in a specific table field

Posted on 2013-12-12
7
218 Views
Last Modified: 2013-12-15
Hi there,

Let´s say I have a table called "tbl_XYZ" with a certain nvarchar field called "content".

Most of data contained in this field starts with (EXACTLY) this:

<p>Solu&ccedil;&atilde;o Sugerida:</p>  

And, after this, there are (irrelevant) different possibilities such as:

<p> ...  

blank space, then <p> ...

<math> or blank space then <math> ...

etc...


In other words, one possible tblXYZ table could be:

fieldID     content
1              <p>Solu&ccedil;&atilde;o Sugerida:</p><p> ...
2              <p>Solu&ccedil;&atilde;o Sugerida:</p> <p> ...
3              <p>Solu&ccedil;&atilde;o Sugerida:</p><math> ...
4              <p>Solu&ccedil;&atilde;o Sugerida:</p> whatever ...
5              <p>  whatever </p>    
6              whatever different from exactly <p>Solu&ccedil;&atilde;o Sugerida:</p> etc

That understood, my question is:

what would be a proper query that would DELETE ONLY the "<p>Solu&ccedil;&atilde;o Sugerida:</p>" expression (and potential blank spaces right after it) presented ONLY in the begining of the content field, and would leave everything else as it is?

In other words, I would like to get the following "answer" to the table shown above:

fieldID     content
1              <p> ...
2              <p> ...   (take out the blank space before this)
3              <math> ...
4              whatever ...  (take out the blank space before this)
5              <p>  whatever </p>   (Leave this exactly as it is)  
6              whatever different from exactly <p>Solu&ccedil;&atilde;o Sugerida:</p> etc
(again leave content of field 6 exactly as it is)

Thanks,
fskilnik
0
Comment
Question by:fskilnik
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39714440
Give this a whirl...
UPDATE YourTable
-- Remove the string from column ContentID
SET ContentID = REPLACE(ContentID , '<p>Solu&ccedil;&atilde;o Sugerida:</p>', '')
-- Only when it is in the beginning of the string
WHERE CHARINDEX('<p>Solu&ccedil;&atilde;o Sugerida:</p>',ContentID, 1) = 1

Open in new window

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39714445
Super quick Jim!

Think you're missing a ' at the start of the CHARINDEX. Edit: Ha! You fixed it too quick too!
0
 

Author Closing Comment

by:fskilnik
ID: 39714486
Thanks a lot, Jim!  (And Angelp1ay too, for sure.)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39714505
I perform my own stunts too.

Thanks for the grade,  good luck with your project.  -Jim
0
 

Author Comment

by:fskilnik
ID: 39714526
Thanks, and best wishes for 2014!  :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39714591
Be aware:

1) That code will replace ALL appearances of the string, so if there were 2 (or more), then ALL would removed.

2) it doesn't remove any leading spaces that might have been left by removing the other string, as originally requested.

3) Finally, this code:
WHERE ContentID LIKE '<p>Solu&ccedil;&atilde;o Sugerida:</p>%'
is potentially much more efficient than the CHARINDEX used.
0
 

Author Comment

by:fskilnik
ID: 39720001
Dear ScottPletcher,

Although the problems stated at (1) and (2) do not occur in my data (and the number of fields involved is too small to "feel" the difference between the choices related to (3)), you were very kind in mentioning the potential problems they could create.

Thank you very much for your input. (I did not login here since closing the post, that´s why my delay.)

Best Regards,
fskilnik.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now