1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

need help with substring functionn in mySql

Discussion in 'MySQL' started by stock_post, Oct 23, 2011.

  1. #1
    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.
     
    stock_post, Oct 23, 2011 IP
  2. georgiivanov

    georgiivanov Member

    Messages:
    62
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    25
    #2
    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):
     
    georgiivanov, Oct 24, 2011 IP
    stock_post likes this.
  3. stock_post

    stock_post Notable Member

    Messages:
    5,214
    Likes Received:
    249
    Best Answers:
    0
    Trophy Points:
    260
    #3
    This is exactly what I am looking for - You are the best..
    Thank you.
     
    stock_post, Oct 24, 2011 IP
  4. nichewriter

    nichewriter Peon

    Messages:
    27
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
     
    nichewriter, Oct 25, 2011 IP
  5. mastermunj

    mastermunj Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    35
    #5
    Following could also be good options:

    
    SELECT SUBSTRING_INDEX('Test1/Test2', '/', 1);
    
    SELECT SUBSTRING_INDEX('Test1/Test2', '/', -1);
    
    Code (markup):
     
    mastermunj, Oct 29, 2011 IP
  6. stock_post

    stock_post Notable Member

    Messages:
    5,214
    Likes Received:
    249
    Best Answers:
    0
    Trophy Points:
    260
    #6
    Thanks guys, I will test the remaining option in the future..
     
    stock_post, Oct 30, 2011 IP