need help with substring functionn in mySql

Please see if you can help with sub string function in MySQL


Say Table1.Field1 = "Test1/ Test2"
Table1.Field1 = "My Test1/This Text"

how do I select Test1
How do I select Test2

These are not fixed length strings.. I need to use "/" field to select string before and string after.

 

Take a look at MySQL String Functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

The answer to your question:

-- find the position of /
select locate('/', 'test1/test2') as colname;

-- select test1
select substr('test1/test2', 1, locate('/', 'test1/test2')-1) as colname;

-- select test2
select substr('test1/test2', locate('/', 'test1/test2')+1) as colname;

Code (markup):

 

This is exactly what I am looking for - You are the best..
Thank you.

 

Another option:
select substring(Field1,1,instr(Field1,'/')-1) from Table1 -- for the first field
select substring(Field1,instr(Field1,'/')+1) from Table1 -- for the second field

 

Following could also be good options:

SELECT SUBSTRING_INDEX('Test1/Test2', '/', 1);

SELECT SUBSTRING_INDEX('Test1/Test2', '/', -1);

Code (markup):

 

Thanks guys, I will test the remaining option in the future..