String manipulation in MySql

Question

Hi friends, I m using mysql. in one field i have inserted data like string1::string2 two string concated by :: now i want to fetch only second string , Could any body help me for this, Thanks in advance

Answer 1

You realize that this is a terrible idea? Relational database systems have columns for a reason. Doing this you are working against the system; this is a well-known antipattern. You should really try to use separate columns for separat values.

That said, you can use MySQL's string manipulation functions to retrieve the values.

This is an overview of MySQL's string functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

There is a comment on the page above explaining how to work with them. Or you can use SUBSTRING_INDEX(), as explained in the other answers.

Answer 2

SELECT   SUBSTRING_INDEX('string1::string2', '::', -1)

Answer 3

As stated earlier, this is a terrible design. You should use multiple columns for multiple dates. That aside, you can use wild-cards to find the value. Say for instance you're looking for 09/08/2009::09/09/2009,

SELECT SUBSTRING('date',-10) 
FROM myTable 
WHERE id = 1;

Which would return the last 10 chars of your date. Again, this isn't by any measure a "good" solution. A goood solution would be to fix the database itself.