Replace Part of String in MySQL

Question

If i have data in my MySQL like this

table:

data(TEXT)

foo (hal)

foo (dave)

bar (dave)

bar(dave)

And i want to do This Query

SELECT DISTINCT(data) FROM table;

Now this will return the table as listed above. But i want to do is some sort of replace so that my return query should look like this

SELECT DISTINCT(data) FROM table Replace(data, "(xxxx)", "");

So the Query returns

Obviously the data is not replaced because the Brackets are important, its just replaced for the query

If this can be done is there performance pitfals in this

can you post your table schema and maybe some sample data so we have a better understanding of what you are trying to do. Also, it sounds like from what you posted, you may be better off with a LIKE query. REPLACE will definitely impact performance — Mark
– Mark, Commented Aug 8, 2009 at 17:55
As shown above. The table name is 'table' and it has one column 'data' Where the fields are shown above — Angel.King.47
– Angel.King.47, Commented Aug 8, 2009 at 18:02

hobodave · Accepted Answer · 2009-08-08 18:02:05Z

2

SELECT DISTINCT stuff 
FROM (
  SELECT RTRIM(SUBSTR(data, 1, LOCATE('(', data) - 1)) AS stuff 
  FROM foo
) t;

answered Aug 8, 2009 at 18:02

hobodave

29.4k4 gold badges74 silver badges77 bronze badges

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

3 Comments

The RTRIM is optional. It strips trailing spaces from your data.

LOCATE('(', data) - 1) Im having probs on that

It works just fine. We can't help you if you just say "Im having problems"

Angel.King.47 · Accepted Answer · 2009-08-08 18:19:41Z

1

This worked for me for the above problem. Thanks hobodave, you did put me to the right direction

SELECT DISTINCT(SUBSTRING_INDEX(data, '(', 1)) AS tag FROM table

answered Aug 8, 2009 at 18:19

Angel.King.47

8,03414 gold badges62 silver badges85 bronze badges