how to deleting part of an entry

say if I have a column containing

hello;world;how;are;you;

how do I write a sql command to just delete "hello w开发者_JAVA技巧orld" or "how are you".

thanks

trojanfoe wrote what you need to do with a correct database design. If you can't change this I may have a possible way todo it. I doubt it is the fastest way to do so, but you can fetch the data, split it into a new virtual table and then remove what you need, the result you can add again in your column.

There is no split in SQL, so here is a split function (MS SQL, maybe need to modify for mySQL):

CREATE FUNCTION dbo.fn_Split(@text nvarchar(4000), @delimiter char(1) = ',') 
RETURNS @Strings TABLE (
position int IDENTITY PRIMARY KEY,
value nvarchar(4000)
)

AS
BEGIN

DECLARE @index int
SET @index = -1

SET @text = RTRIM(LTRIM(@text))

WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))

END
RETURN
END

With this you can do stuff like this:

SELECT * FROM dbo.fn_Split(<yourcolumn>,';')

Which gives you then your content as single rows. Then you can remove items by using a WHERE statement like

SELECT * FROM dbo.fn_Split(<yourcolumn>,';') WHERE [value] LIKE '%hello%'

You might need to write a PROCEDURE then or a TABLE/SCALAR FUNCTION to handle you data with this.

So isn't a good way to do but the only way I found out so far :) Hope it helps a but.

If this column contains multiple items that you want to manipulate then putting them into a single column is a schema design flaw.

You are not taking advantage of the relational nature of the database and you should store these items in a child table of the main table. You will then be able to define ordering in this child table (with a separate column) and will be able to manipulate (i.e. SELECT, DELETE, etc) individual elements far easier.

Guess if it's a legacy database structure it cannot be changed easily.

Btw the answer is updating the field. Since almost every database engine nowadays allows you to write a stored function, I guess that's a good solution.

Using Oracle's Syntax something like this would work:

UPDATE TABLE SET FIELD_NAME = STORED_FUNCTION_MANIPULATING_THE_FIELD(FIELD_NAME) WHERE [where condition]

but before executing the query, you should define the stored function.

FUNCTION STORED_FUNCTION_MANIPULATING_THE_FIELD(FIELD_NAME VARCHAR2) RETURN VARCHAR2 IS
BEGIN
   -- Do whatever you want on field, then return it.

END STORED_FUNCTION_MANIPULATING_THE_FIELD;

However this is conceptually wrong, multiple data values, shall use multiple columns in order to take fully advantage of relational engines (as stated in previous answers).

Regards

M.

UPDATE tablename SET field=replace(replace(field,'hello;world;',''),';',' ') [WHERE condition]

UPDATE tablename SET field=replace(mid(field,locate('how',field)),';',' ') [WHERE condition] 

UPDATE tablename SET field=replace(mid(field,1,locate(';how',field)),';',' ') [WHERE condition] 

UPDATE tablename SET field=replace(substring_index(field,';',2),';',' ') [WHERE condition] 

(If you use a -2 in the substring_index function, you get the other part of the text.)

One of those will give you what you want. The use of substring_index is probably the most flexible way.