Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    8

    Unanswered: Split string with a special character

    Hello first of all sorry for my english. I have a problem, i need separate the text from a tweet in some fields in sql. The text is for example:

    "@user1 @user2 Description of tweet http://url.com"

    i need separate the text of this form:

    Users || Descriptions || URL
    ----------------------------------------------------------------------
    @user1 @user2 || Description of tweet || http://url.com

    Tanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    based upon what criteria are USER differentiated from DESCRIPTION & differentiated from URL?
    are these types always in the same order?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    8
    thanks for replying, the criterion for USER, it always starts with "@", DESCRIPTION is only text and URL always begins with "http". The text may have one, two or three users but all start with @ and some times has no URL

    The DB is a recopilations of tweets

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> with test as
      2    (select 1 id, '@user1 @user2 Description of tweet http://url.com' col from dual
      3     union
      4     select 2, '@user Another description http://another.url.com' from dual
      5     union
      6     select 3, '@usr1 @usr2 @usr3 The third desc http://final.url.hr' from dual
      7    )
      8  select
      9    id,
     10    regexp_substr(col, '(@\w+ )+') r_user,
     11    regexp_replace(regexp_replace(col, '(@\w+ )+', ''), 'http.+', '') r_desc,
     12    regexp_substr(col, 'http.+') r_url
     13  from test;
    
     ID R_USER               R_DESC                    R_URL
    --- -------------------- ------------------------- -------------------------
      1 @user1 @user2        Description of tweet      http://url.com
      2 @user                Another description       http://another.url.com
      3 @usr1 @usr2 @usr3    The third desc            http://final.url.hr
    
    SQL>
    (I used the ID column in order to preserve some space in a line).

  5. #5
    Join Date
    May 2012
    Posts
    8
    hi, thanks again for answering and sorry for not writing sooner. I try to use regexp_substr but I have an error that the function does not exist, I use MySQL I guess that's a PLSQL function. What function should I use?

    give examples of some records from my database to give a better idea of what I do ... thanks


    @TWCHack11 Hackers present - for live video stream go to http://t.co/jNDyKnb #SemWeb #AppsForScience #HackForScience #LinkedData

    @mhausenblas @DublinCore: CfP: IEEE IS Special Issue on Linked Open Gov. Data http://bit.ly/kIWxf8 #linkeddata

    @johanoomen: W3C Library Linked Data Incubator Group Final report and call for comments: http://bit.ly/mpWzz8 #linkeddata #opendata

    USER || Descriptions || URL || TAG
    --------------------------------------------------------------------------------------------------------------------------
    @TWCHack11 || Hackers present - for live video stream go to || http://t.co/jNDyKnb || #SemWeb #AppsForScience #HackForScience #LinkedData

    @mhausenblas @DublinCore || CfP: IEEE IS Special Issue on Linked Open Gov. Data || http://bit.ly/kIWxf8 || #linkeddata

    @johanoomen || W3C Library Linked Data Incubator Group Final report and call for comments: || http://bit.ly/mpWzz8 || #linkeddata #opendata

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I use MySQL I guess that's a PLSQL function.
    Then why do you post to this Oracle forum?
    Go & post in MYSQL forum

    http://www.dbforums.com/mysql/
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2012
    Posts
    8
    ok, I will do thanks

Tags for this Thread

Posting Permissions

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