Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mb_substr similar function in mysql?

    What i am looking for is a function similar to substr($str,1 100) to use in a query,hoping:> I could get certain length of UTF8 charecters.

    My table contains utf8 charecters in a 'body' field(pressumabaly utf8), inserted via web form using unicode charecters. I need to pull only 200 charecters of the fiel 'body', using substr(body, 1, 200) wouldn't work because these are utf8 charecters, hence is there a way to do this, mb_substr(..) functions doesn't exist in mysql.

    I am using mysql 5.1 version, my table 's charecter set is UTF8 collated utf_unicode_ci, Storage Engine: InnnoDB

    When i look at the table, the inputs in body field which was inserted via web using unicode, are displayed in html entities. for example, a Devnagari unicode alphabet :


    is stored as:
    #2326 [begins with '#' and ends with ';' I can't type the whole of it here because it will be displayed as क when you see it]

    which is 7 ascii charecters. This not only implies is taking lot of space in database table but gives me no option(since i dont' know how to) to pull certain number of charecter ie a sub string. Like example above, 7 charecters equal 1 single complete charecter in Devnagari unicode, so how do i go around to fetch , say only 100 devnagari complete charecters from a table, which would mean I would have to pull 100 * 7 charecters to equal to 100 complete Devnagari chars. But what is in the data field is dynamic so what is the way to go around this??

    I posted a thread in forum.mysql.com about his issue but i was suggested to go to XML forum to find answer that really made me feel I am not the only fool around here.

    Hope someone can suggest me a way out of this.
    Thanks

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    substring and mid are multibyte safe, so your problem I guess is how do you manipulate and store data in mysql and html. One reason why I belive this is the output as entities( mysql don't make any implicit conversion to/from charset to entities)
    check your mysql data, I guess you already store entites in that field.

    best regards


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •