CODEWITHSUNDEEP
mysqlregexhashuser-defined-functions
Ha Dev
Ha Dev

Reputation: 93

To fetch hash tags from a string in mysql

Friends, I want to fetch hashtags from a field.

select PREG_RLIKE("/[[:<:]]abcd[[:>:]]/","okok got it #abcd"); 
//output 1

BUT

select PREG_RLIKE("/[[:<:]]#abcd[[:>:]]/","okok got it #abcd"); 
//output 0

not getting why # is not considering Please help

Upvotes: 2

Views: 399

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626926

The pattern matches:

  • [[:<:]] - a leading word boundary
  • #abcd - a literal string
  • [[:>:]] - a trailing word boundary.

Since a leading word boundary is a location between a non-word and a word char (or start of a string and a word char), you can't expect it to be matched between a space (non-word char) and a hash symbol (#).

Since you are using a PCRE based UDF function, use lookarounds:

select PREG_RLIKE("/(?<!\\w)#abcd(?!\\w)/","okok got it #abcd");

The (?<!\w) negative lookbehind acts like a leading word boundary failing the match if the search term is preceded with a word char, and (?!\w) negative lookahead fails the match if the search term is followed with a word char.

See the regex demo.

Upvotes: 1

Related Questions