?
Solved

delete portion of data in a field

Posted on 2009-12-30
25
Medium Priority
?
295 Views
Last Modified: 2012-08-14
I have the query below that deletes data from a field, the problem is that the code that is displayed sometimes is cut off, so it wont work because its not identical to the junk that was added.

My QUery:

SET QUOTED_IDENTIFIER OFF
update activities
set partylist = replace (partylist,  "certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://tests4all.org/4/'>the bluest eyes in texas paraody</a><a href='http://tests4all.org/5/'>sayings cheated off the rode</a><artner parts</a><a href='http://tests4all.org/8/'>printable kids iq test</a><a href='http://tests4all.org/9/'>c liv", '' )


Sometimes is just:  

certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://

so, what i need is to delete whatever is in the field after the words 'certifications are accredited' .. that and anything after that should be deleted. Instead of the exact text above.

Help is appreciated.

0
Comment
Question by:Aleks
  • 12
  • 9
  • 3
  • +1
25 Comments
 
LVL 8

Expert Comment

by:Jon500
ID: 26151781
It's really difficult to understand what you are asking, but I will try to help (for example, I have no idea what you meant by "junk" that gets added)...

Why not take a different approach? Why not do a Select with a query that looks for the column name and then use LIKE 'certifications are accredited%'. This should return all rows with columns containing text beginning with those words. You can then use this select result in an update query to set those fields to ''.

Please let me know what you think.

Regards,
Jon500
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26151819

update activities
set partylist =  'certifications are accredited'
WHERE partylist like 'certifications are accreditedfree quit smoking tool
0
 

Author Comment

by:Aleks
ID: 26151842
Well the database field has a value, say 'romero', a hacker entered that 'junk' after the original value, it always starts with 'certifications .. bla bla' but sometimes it gets cut off before the end, so i cant compare it.

so before it was 'romero' now its 'romerocertifications ... ' etc.

so anything from 'certifications' and afterwards should be deleted.

A
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 8

Expert Comment

by:Jon500
ID: 26151881
What you can do is use the CHARINDEX function. This will be >0 if the substring you're looking for is found with the string you provide.

Thus, if you have a table TABLE with a column NAME, you can do this:

SELECT * FROM TABLE WHERE CHARINDEX('certifications are accredited', NAME) > 0

Once again, if you get the rows you're looking for, you can then update the NAME column, as I suggested above.

Do you think this might work for you?

Regards,
Jon500
0
 

Author Comment

by:Aleks
ID: 26151901
no no ,, i want to delete 'certifications are acredited and everything afterwards, not to make it the same.
the only thing that stays the same is whatever is before that sentence.
0
 

Author Comment

by:Aleks
ID: 26151941
See, the code below keeps the value of the field whatever is before the word 'certifiactions' and deletes the other stuff but only if its exactly alike,  i need one that deltes whatever is after the word 'certifications'

SET QUOTED_IDENTIFIER OFF
update activities
set partylist = replace (partylist,  "certifications are accredited</a><a href='http://tests4all.org/3/'>free quit smoking tool</a><a href='http://tests4all.org/4/'>the bluest eyes in texas paraody</a><a href='http://tests4all.org/5/'>sayings cheated off the rode</a><artner parts</a><a href='http://tests4all.org/8/'>printable kids iq test</a><a href='http://tests4all.org/9/'>c liv", '' )


A
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26151969
Oh, sorry. I think I get it now...

We already discussed CHARINDEX, which will find the starting point of your offending text (X). There is another function, SUBSTRING. You can set the column value to the substring that contains only your wanted text.

The substring you want will always begin at character position 1, based on what you said above. You then want the substring starting at 1 and ending at the character before the offending text.

This gives something like this:
SUBSTRING(column, 1, X+1)

Where X is the value that CHARINDEX('certifications are accredited', column) returns.

So instead of setting columns to '', as I suggested earlier, set them to this value:
SUBSTRING(column, 1, CHARINDEX('certifications are accredited', column) - 1)

Remember, your where clause should be using the CHARINDEX function to screen rows that have CHARINDEX > 0. This should work even if the string in the column consists ONLY of the offending text (in which case the CHARINDEX function will return 1).

Does this help?

Regards,
Jon500
0
 

Author Comment

by:Aleks
ID: 26151999
Not really. I wouldnt know how to create the script :(
0
 

Author Comment

by:Aleks
ID: 26152043
isnt there a way to use %" instead of the exact value ?

SET QUOTED_IDENTIFIER OFF
update activities
set partylist  = replace (partylist,  "certifications are accredited%", '' )

With THE % SIGN AT THE END ? or something ?
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26152357
The script looks like what I show below. You can comment out the COMMIT TRANSACTION line with two dashes (--) if you want to try the query before committing it.

Regards,
Jon500
BEGIN TRANSACTION

UPDATE Activities SET PartyList =
SELECT
      Substring(PartyList, 1, CHARINDEX('certifications are accredited', PartyList) - 1)
FROM
      Activities
WHERE
      CHARINDEX('certifications are accredited', PartyList) > 0

COMMIT TRANSACTION

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 26154132
>>isnt there a way to use %" instead of the exact value ? <<
No.
>>so, what i need is to delete whatever is in the field after the words 'certifications are accredited' .. that and anything after that should be deleted<<
This is the same thing as "what i need is to keep whatever is in the field before the words 'certifications are accredited ...' which is what Jon500 had provided you.
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26154219
@awking00: Thank you. I was starting to wonder if I might have missed something. Let's see if there is any further follow-up to this. I really wanted to help this guy.
0
 

Author Comment

by:Aleks
ID: 26154485
I will run this today and let you know. Meantime thank you all for your help. Its just that we went through an injection attack and I am trying to cleanup the database. But our server this morning is real slow and i think we might be under the same type of attack, i am looking into it then finalize and run the query above. Ill let you know. I am testing locally first.

Aleks
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26154583
Thanks for the follow-up and for recognizing our respective efforts.

Cheers,
Jon500
0
 

Author Comment

by:Aleks
ID: 26154597
When running the code below:

--
BEGIN TRANSACTION
 
UPDATE Activities SET PartyList =
SELECT
      Substring(PartyList, 1, CHARINDEX('certifications are accredited', PartyList) - 1)
FROM
      Activities
WHERE
      CHARINDEX('certifications are accredited', PartyList) > 0
 
COMMIT TRANSACTION
--

I get the following error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.

0
 

Author Comment

by:Aleks
ID: 26154617
I can run the code below just fine, its when i add the 'Update activities set partylist = '  when i get the error.

this runs fine:

SELECT
      Substring(PartyList, 1, CHARINDEX('certifications are accredited', PartyList) - 1)
FROM
      Activities
WHERE
      CHARINDEX('certifications are accredited', PartyList) > 0
0
 
LVL 8

Accepted Solution

by:
Jon500 earned 1200 total points
ID: 26154741
Sorry, please add a left parenthesis and right parenthesis around the SELECT clause, as shown below:


BEGIN TRANSACTION

UPDATE Activities SET PartyList =
(SELECT
      Substring(PartyList, 1, CHARINDEX('certifications are accredited', PartyList) - 1)
FROM
      Activities
WHERE
      CHARINDEX('certifications are accredited', PartyList) > 0 )

COMMIT TRANSACTION

Open in new window

0
 

Author Comment

by:Aleks
ID: 26154756
I got this error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 800 total points
ID: 26154782
See attached.
comments.txt
0
 

Author Comment

by:Aleks
ID: 26154794
You were right. This is now solved with the following query:

UPDATE Activities
SET PartyList = Substring(PartyList, 1, CHARINDEX('certifications are accredited', PartyList) - 1)
WHERE CHARINDEX('certifications are accredited', PartyList) > 0

Thank you guys !

A
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26154802
Give me 15 mins. I wasn't running your code because I don't have your tables, but I want to run the actual code and get this working once and for all. I will be actively working on this NOW. You're close to a solution.
0
 

Author Closing Comment

by:Aleks
ID: 31671632
Great working with you guys !!!  Thanks for the patience, we got this one.
Not sure how to balance the points tho ..I am just giving a bit more to John cuz he provided the inital solution and followed up, if it was for me I would give you both 500.
0
 
LVL 8

Expert Comment

by:Jon500
ID: 26154877
@awking00: Thank you for assisting. I had started with a nested select not realizing that it was unnecessary in this case.

@amucinobluedot: Sorry for the faux pas but I'm thrilled you're up and running!
0
 

Author Comment

by:Aleks
ID: 26154900
Thank you again !  This will cleanup my database and get me back to normal. Just this is worh a year of service with expert exchange, if not more.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26154938
Glad to be of assistance. Happy New Year to all!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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