Solved

deleting specific string in a specific table field

Posted on 2013-12-12
7
223 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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