MYSQL Format Data in a Column

Question

I Have a question on formatting data in MYSQL.

This is how my table looks like

Table Movie
ID             Movie           Cast
1              Movie1          Actor1(Role1), Actor2(Role2), Actor3(Role3)
2              Movie2          Actor2(Role4), Actor3(Role1)
3              Movie3          Actor3(Role7), Actor5(Role9), Actor2(Role4)

I understand having multiple values in a field is bad database design and I am working on separating the values as well.

Can anyone helped me on removing the (Roles) from the column Cast? Is there anyway to check "(" and ")" and remove all characters in between the brackets?

Thanks for any help!

Including the brackets themselves?

RichardTheKiwi
– RichardTheKiwi

2011-03-18 03:47:44 +00:00
Commented Mar 18, 2011 at 3:47 — RichardTheKiwi
– RichardTheKiwi, Commented Mar 18, 2011 at 3:47

Nicola Cossu · Accepted Answer · 2011-03-18 03:38:42Z

2

Take a look at this function

http://forge.mysql.com/tools/tool.php?id=265

answered Mar 18, 2011 at 3:38

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ggiroux · Accepted Answer · 2011-03-18 05:22:13Z

1

This SQL function should get you going, from a user comment in mysql's manual, if you adjust for parenthesis instead of brackets.

delimiter ||
DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
SET sstart = LOCATE('<', x, 1);
REPEAT
  SET ends = LOCATE('>', x, sstart);
  SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
  SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
return x;
END;
||
delimiter ;

mysql> SELECT strip_tags('<a href="HelloWorld.html"><B>Hi, mate!</B></a>') as strip_tags;

+------------+`
| strip_tags |`
+------------+`
| Hi, mate!  |
+------------+

edited Mar 18, 2011 at 5:22

answered Mar 18, 2011 at 4:45

ggiroux

6,7441 gold badge25 silver badges23 bronze badges

Comments

fmpwizard · Accepted Answer · 2011-03-18 04:32:05Z

You can also run this query 3 times and it will delete the 3 parenthesis and the content inside them.

I could've made it into one query, but I thought it would be too long and it would be harder to modify (error prone)

UPDATE table_name SET cast=REPLACE( cast ,  SUBSTRING(cast, LOCATE("(", cast)   , ( LOCATE(")", cast) - LOCATE("(", cast) +1  )) , ""     ) ;

If you split the query, you see that:

we get the length in characters between the opening and closing parenthesis, the +1 is there to include the closing parenthesis)

( LOCATE(")", cast) - LOCATE("(", cast) +1  )

Here we select the text from the cast column, starting at the first occurrence of ( and the length is taken from the line above snippet.

SUBSTRING(cast, LOCATE("(", cast)   , ( LOCATE(")", cast) - LOCATE("(", cast) +1  ))

Finally we use the REPLACE function, which takes the result of the previous line as parameters.

All those functions are documented here:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Collectives™ on Stack Overflow

MYSQL Format Data in a Column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related