CODEWITHSUNDEEP
javamysqlpostgresqlsearchhashtag
Kamini
Kamini

Reputation: 690

fetch only related values of hashtags from name in search functionality

I am working in java and postgresql.
I have one table name 'videos' and one of the field in it name 'video_name', so the video name can contain the #tag values like for ex.

  1. my fav star #tom
  2. enjoyed weekend #friends#party masti
  3. had dinner at #seefood with #friends
  4. walk with #friendsEnjoy

so now in this case i want to have search on this field 'video_name' with only hashtags. if i search for #friends i will get the number 2,3 videos.

i wanted to know what should be query in this case

i have tried using query 

select * from videos where video_name ilike '%#Friends'

but it is not giving me exact results.

I have also tried using this query 

 select * from videos where video_name ~* '#friends'

but using above query i got 3 results 2,3,4. and i want only 2 & 3.

/*******************************ANSWER***********************************/

Here is the answer of the above question. thanks @Alex videos which include hashtag with space ahead of the string and again new #tag ahead of that string.

SELECT * 
FROM videos
WHERE lower(video_name) SIMILAR TO '%#friends(\s%|\#%)?'

Upvotes: 2

Views: 311

Answers (1)

Alex
Alex

Reputation: 17289

SELECT * 
FROM videos 
WHERE video_name REGEXP '#friends'

and here is postgresql version if you need:

SELECT * 
FROM videos
WHERE video_name SIMILAR TO '%#friends%'

and here is example how to resolve case sensitiveness

SELECT * 
FROM videos
WHERE lower(video_name) SIMILAR TO '%#friends%'

EDIT And another variant with space check:

SELECT * 
FROM videos
WHERE lower(video_name) SIMILAR TO '%friends(\s%|\#%)?'

Upvotes: 1

Related Questions