Solved

deleting specific string in a specific table field

Posted on 2013-12-12
7
219 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

867 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

15 Experts available now in Live!

Get 1:1 Help Now