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

Before you Insert the data, check the string for the domain name and use Replace() to replace it with a blank.

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

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

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
function in DataBase.

Always:),
Hovik Melkomian.

   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 = '[email protected]'

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

My apolgies, my example deletes the rows. Use a trigger as melvik suggested and use a REPLACE to replace the string with blank ('').

Hello,

Can I look for examples somewhere please as I don't know how to create a trigger.
I will really appreciate it.

Palvin