Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

deleting specific string in a specific table field

Posted on 2013-12-12
7
Medium Priority
?
225 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 70

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

604 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