|
Subject:
|
Deleting text strings automatically from Database
|
|
Posted By:
|
palvin
|
Post Date:
|
8/5/2005 10:56:46 AM
|
Hello,
How can I automatically remove a particular text string from my database columns? Here is what I want to do...
I have a database on SQL 2000 server which stores the information about users. I want that when a user enters their profile information, If somebody enters any particular words like "domain.com" or any other string, it is automatically deleted from that column without the user being notified, so that if somebody views that profile those words are deleted from their profile.
Thanks in advance,
Palwinder
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
8/5/2005 1:53:00 PM
|
Before you Insert the data, check the string for the domain name and use Replace() to replace it with a blank.
|
|
Reply By:
|
palvin
|
Reply Date:
|
8/5/2005 3:50:34 PM
|
Thanks for your reply Jbenson. Is there any examples or illustration to this process as I am not a master of SQL. I just started SQL 3 weeks ago.
Thanks
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
8/5/2005 11:55:22 PM
|
Do you have a list of specific strings that you don't want inserted? If so, you can create a table and enter them, then join the 2 tables, and delete if there is a match. I will explain more. First let me know if you have a list of strings that you want to exclude.
|
|
Reply By:
|
palvin
|
Reply Date:
|
8/6/2005 6:25:55 AM
|
quote:
Originally posted by jbenson001
Do you have a list of specific strings that you don't want inserted? If so, you can create a table and enter them, then join the 2 tables, and delete if there is a match. I will explain more. First let me know if you have a list of strings that you want to exclude.
I don't want anybody to write "Yahoo, hotmail, mail, @, .com" I think if you can help me in this then later on I can modify the code to add or remove other strings.
Thanks
|
|
Reply By:
|
melvik
|
Reply Date:
|
8/6/2005 11:56:36 PM
|
Dear Palvin:
As jbenson001 mentioned its better to have atable included unwanted texts & join it with ur inserting data. u can also use it in trigger
but if ur words r really less than 10 & its fix & wont be more u can use
REPLACE function in DataBase.
Always ,
Hovik Melkomian.
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
8/6/2005 11:58:47 PM
|
Create a table with a column that contains strings you want to exclude: Say the table has a column named ExcludeString defined as varchar(30) for example.
Let the user enter whatever they want, and insert it into your profile table. (Let's say that column is named EMail)
After the insert into your profile table, delete if they have entered a string you want to exclude:
Delete From Profile --assuming this is the name of your main table, change as needed.
From profiles p
Inner Join exclusions e ON --exclusions is the table of strings you want to exclude ... change as needed
CHARINDEX(e.excludestring, p.email) > 0
*** Note that entering a string like 'mail' will remove any email with 'mail' in the string which will also remove is for example an email = 'abc@hotmail.com'
|
|
Reply By:
|
palvin
|
Reply Date:
|
9/13/2005 11:52:15 AM
|
Hello Jbenson001,
I am sorry I was away for one month. So was not able to reply. I tried your suggestion. It is working, but it just deletes the whole user account. If user comes back to logon, it says account does not exists.
Here is how I am using it
Delete From UserInfo
From UserInfo p
Inner Join Exclusion e ON --exclusions is the table of strings you want to exclude ... change as needed
CHARINDEX(e.exclusions, p.AboutYourself) > 0
Now the UserInfo table includes all the fields about user's profile including username n password. So I guess right now it is deleting the whole row.
Please suggest.
Thanks,
Palvin
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
9/13/2005 12:28:11 PM
|
My apolgies, my example deletes the rows. Use a trigger as melvik suggested and use a REPLACE to replace the string with blank ('').
|
|
Reply By:
|
palvin
|
Reply Date:
|
9/13/2005 12:54:05 PM
|
Hello,
Can I look for examples somewhere please as I don't know how to create a trigger.
I will really appreciate it.
Palvin
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
9/13/2005 1:16:43 PM
|
See Books On Line that comes with Sql Server. Also you can search on Google.
|
|
Reply By:
|
Stuart Stalker
|
Reply Date:
|
10/1/2005 6:12:03 PM
|
If they are submitting through a web page surely it would be easier to do this when they submit it, so it never even hits the db?
|
|
Reply By:
|
palvin
|
Reply Date:
|
10/3/2005 8:29:55 AM
|
Hello,
I tried a vbscript to do that but it was giving errors. Is there anything you can suggest me to get this done please?
I will be thankfull.
Palvin
|
|
Reply By:
|
robprell
|
Reply Date:
|
10/3/2005 11:09:41 AM
|
Just for clarification. Do you want to replace from a specific list like (yahoo.com, hotmail.com, micro$oft.com) or ALL (*.com, *.net, *.org) type values? Because the second option is much more difficult.
|
|
Reply By:
|
palvin
|
Reply Date:
|
10/3/2005 12:08:48 PM
|
Hello,
Even if I am able to go by the first option, it will be great. Then I will omit certain words like hotmail, hotmail.com, hotmaildotcom etc.
Thanks.
|
|
Reply By:
|
robprell
|
Reply Date:
|
10/3/2005 12:52:01 PM
|
Ok then in that case I suggest you do in in the front end app like suggested above. If you do it in SQL I suggest the below listed process. But I suggest you use both. Filter them in the front end then if new vales you don't like make it to the database. You can remove them and revise the front end with additional exceptions. Create a table of the values you want to exclude. Run a nightly job the compares the exclude list to whats been entered. This is easier than doing the trigger approach but the disadvantage is you allow the saving of the data you don't want until the job is run. The advantage is if you want to grow your list of exceptions it will go back and fix all historical instances of what you don't want. If you want the best of both worlds you could compare against the list in the trigger. But the disadvantage of this is it will be extremely slow. As your list grows the trigger method will also be be slower and slower. Question to ask yourself is can you live with the undesirable data for a period of time. Best solution still is on the front end app. My suggestions are only if you want/have to do it in the backend and to get you to think about the ramifications of the direction you choose.
|
