?
Solved

deleting specific string in a specific table field

Posted on 2013-12-12
7
Medium Priority
?
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 66

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:Scott Pletcher
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 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