CODEWITHSUNDEEP
mysql
user2503896
user2503896

Reputation:

how to remove single quote ' character in mysql text field?

This is my query:

SELECT count(email_details.id) as total_emails,
       email_titles.* 
  FROM email_details 
  LEFT JOIN email_titles ON email_details.email_title = email_titles.title 
 GROUP BY email_details.email_title 
 ORDER BY email_titles.id ASC

Now my problem is email_details.email_title is blob field and email_titles.title is text field, both field has a data with ’ chracter say "this month’s video" when i runs this query without left join the row having ’ chracter does not retrieve, if i put left join then it retrieves only first table data. how to solve this problem?

enter image description here enter image description here

Upvotes: 1

Views: 399

Answers (2)

user2503896
user2503896

Reputation:

I have solved my problem by my self, i have modified join code like this

LEFT JOIN `email_titles` ON CONVERT( email_details.email_title
USING utf8 ) = CONVERT( email_titles.title
USING utf8 )

Upvotes: 1

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

The best option would be to join on something other than those large chunks of text or if that is not possible, make sure that the same text is stored in the same way on both tables.

If that isn't possible either, you need to convert the blob into text for the join, possibly also convert the varchar into char. Something like this

SELECT count(email_details.id) as total_emails,
       email_titles.* 
  FROM email_details 
  LEFT JOIN email_titles 
    ON email_details.email_title = CAST(email_titles.title AS char(100) CHARACTER SET utf8) 
 GROUP BY email_details.email_title 
 ORDER BY email_titles.id ASC

Upvotes: 0

Related Questions