How to filter out the items between the delimiter ";"?

Hello,

Inside a column I have this result:
Record 1: Sales;Admins
Record 2: ;Sales;Admins
Record 3: Sales;
Record 4: Admins;

You can see the delimiter ";", it can be everywhere.

Now I want to delete "Sales".
Therefor I have to search where the "Sales" is. (records)
After that I want to delete the "Sales".

If I delete it the record may not have 2 or more delimiters after each other, like here:
Record 1: ;Admins (good, better is to remove the delimiter also)
Record 2: ;;Admins (bad)
Record 3: ;(good, better is to remove the delimiter also)
Record 4: Admins;

Can somebody help me how to build this query?

Thanks!check for more info on REPLACE function ...
This example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx')
GO

hth|||Sounds like someones breaking their database rules, tut tut ;)|||First normal... second normal... I know but this is simpler then make a lot of database hits each time.|||How smart is the replace function?

Let's say that I have this:
sales;adminsales;admins

I want to replace sales, this means that it must look like this:
adminsales;admins

It may not change adminsales, this is wrong:
admin;admins

Is the replace function a good option for me?

(Is there not something like a split function?)

Thanks|||If you MUST do this, then you'd be much better off always enclosing the data with delim's. E.g. ;sales;;adminsales;;admins;

That way you can use the replace without having the worries you've mentioned for the sake of a few bytes.|||Then I have to search for ";sales;" in this example?

replace(mycolumn,';sales;','')

|||yes, but providing you don't allow ";" then you won't suffer from those Admin or AdminSales problems. I hate all the edge cases you have to worry about with CSVs.