| Finding SQL Query ... [message #631819] |
Tue, 20 January 2015 03:25  |
 |
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
Hello,
I have a table which contains data downloaded from Twitter. This is for Sentiment Analysis.
There are 2 columns in this table, which I am interested in: ID and HASHTAGS.
ID is the tweet ID and HASHTAGS contain the hash-tags used by users while writing the tweets.
Here is a sample 10 rows.
"545852174300102656 " ""text":"badservice","text":"fedup" "
"545855638132523008 " ""text":"02priority","text":"HappyChristmas" "
"545856704882409472 " ""text":"EE","text":"Cuba" "
"545857413686263808 " ""text":"O2" "
"545860412466814976 " ""text":"conningrobbingbastards" "
"545865024557035520 " ""text":"not" "
"545865508445507584 " ""text":"outofpocket" "
"545869655198666752 " ""text":"fail" "
"545870864978554880 " ""text":"iphone5s" "
"545871090372071424 " ""text":"Hellothree" "
Here is the SQL query i use to generate this:
select id, replace(replace(replace(replace(hashtags,'[',''),'{',''),'}',''),']','') from TWITTER_DATA where HASHTAGS like '%text%' and rownum < 11;
Now, my requirement is:
I want to have a view in place, where I can have output like the below. This is to simply have one row per hashtag and the associated ID.
"545852174300102656 " "text":"badservice"
"545852174300102656 " "text":"fedup"
"545855638132523008 " "text":"02priority"
"545855638132523008 " "text":"HappyChristmas"
"545856704882409472 " "text":"EE"
"545856704882409472 " "text":"Cuba"
"545857413686263808 " "text":"O2"
"545860412466814976 " "text":"conningrobbingbastards"
"545865024557035520 " "text":"not"
"545865508445507584 " "text":"outofpocket"
"545869655198666752 " "text":"fail"
"545870864978554880 " "text":"iphone5s"
"545871090372071424 " "text":"Hellothree"
Any help will be highly appreciated.
Thanks
Suddhasatwa
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Finding SQL Query ... [message #631831 is a reply to message #631826] |
Tue, 20 January 2015 06:26  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:For the other test case, do you want me to continue the thread, since I thought we have already resolved it.
No, you have a solution and you posted it, so the thread has be seen as complete like this.
But post the query for this topic we currently are on.
[Edit: typo]
[Updated on: Mon, 06 July 2015 13:28] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
