-2

I need to parse the retweet username from a tweet string using a mySQL query.

A field would look like "RT @sampleuser: some tweet text, @useridonotwant more text"

I need to return only "sampleuser" (to populate another column with). Can this be done with mySQL?

Thanks!

Update: As requested, what I have found out so far is that mySQL does not provide a method of returning substrings via regex.

I tried using this substring solution found here:

SUBSTRING(
haystack,
LOCATE('@', haystack) + CHAR_LENGTH('@'),
LOCATE(
    ':',
    haystack,
    LOCATE('@', haystack) + CHAR_LENGTH('@')
)
- (LOCATE('@', haystack) + CHAR_LENGTH('@'))
) as string
FROM (SELECT FROM schema_name.table_name AS hastack)

But that returns a 1064: you have an error in your sql syntax.

Community
  • 1
  • 1
Dave Molinero
  • 474
  • 1
  • 4
  • 13
  • why you go for mysql. it will assign overwork to the DB engine – Sagar V Feb 27 '17 at 09:15
  • @Fallenhero Thanks for the feedback, I updated to include what I have tried so far, however, I don't know if it's on the right track at all. – Dave Molinero Feb 27 '17 at 09:45
  • @SagarV Well, I have an existing database that isn't linked to anything yet which I need to pass off to team members with an added column containing retweet usernames. – Dave Molinero Feb 27 '17 at 09:48
  • The 1064 is useless without the "near..." part of the error message. – Rick James Feb 27 '17 at 22:49
  • Thanks Rick, I'm not really hoping someone will correct a syntax error. I'm really just hoping someone can point me in the right direction for what I should be using to do this eg regex, substring, etc. I can't find any resources that talk about how to do this in MySQL. – Dave Molinero Feb 27 '17 at 22:56

1 Answers1

0

This answer works for this specific case for anyone looking to parse retweet usernames, but I'm sure there is a more elegant solution.

SELECT <column_name>, SUBSTRING(
    <column_name>,
    LOCATE('@', <column_name>) + 1,
    LOCATE(':', <column_name) - 1 - LOCATE('@', <column_name>)
    )
    FROM <your_schema.your_table>
    WHERE <condition>
Dave Molinero
  • 474
  • 1
  • 4
  • 13