Escaping Hindi Characters in mysql

Question

I'm trying to fetch data from database using MySQL, the table has some entries in hindi, I'm looking to filter out those hindi results in google sheets automatically by running SQL queries in google sheets.

I've been able to fetch data into google sheets from database but unable to fetch hindi filtered data

select property, week,
  sum(Original_Stories) "Original Stories",
  sum(Wire_Stories) "Wire Stories",
  sum(Photo_Gallery) "Photo Gallery",
  sum(Video_Gallery) "Video Gallery",
from
  (select *, 
     case when a= "Original_Stories" then total end as "Original_Stories", 
     case when a= "Wire" then total end as "Wire_Stories", 
     case when a= "Photo" then total end as "Photo_Gallery", 
     case when a= "Video_Gallery" then total end as "Video_Gallery"
   from
     (select "Zee Hin" as property,
        yearweek(date_format(date_published,"%Y-%m-%d"),6) week,
        case
          when ((article_type like 'live_blog') or
                (article_type like '%article%' and agency_name like 'ज़ी न्यूज़ डेस्क') or
                (article_type like '%article%' and agency_name like 'एक्सक्लूसिव') or
                (article_type like '%article%' and agency_name like 'ज़ी मीडिया ब्‍यूरो') or
                (article_type like '%article%' and agency_name like 'Exclusive'))
            then "Original_Stories" 
          when (article_type like '%gallery%') then "Photo"
          when (article_type like '%video%') then "Video_Gallery"
          else 'Wire'
        end as a,
        count(distinct article_id) total,
        count(distinct date_format(date_published, "%Y-%m-%d")) days
      from
        zeenews_hindi
      where date_format(date_published, "%Y-%m-%d") >= '2018-12-30'
      group by 2,3
    ) temp
  ) agg
group by 1,2

I need data for hindi filters

Refer this link :: https://stackoverflow.com/questions/6859018/save-data-in-arabic-in-mysql-database — Mariselvam, Sep 19 '19 at 12:01

score 1 · Answer 1 · answered Sep 19 '19 at 12:29

See this: How can I find non-ASCII characters in MySQL?

With MySQL you can detect non-roman characters with this kind of query.

SELECT whatever
  FROM tableName 
 WHERE columnToCheck <> CONVERT(columnToCheck USING latin1)

This works by comparing your column to its own value rendered in latin1 (aka iso8859-1), a character set suitable for Western Europe and the USA.

For example, this query

SELECT 'Some Hindi: ज़ी मीडिया ब्‍यूरो', CONVERT ('Some Hindi: ज़ी मीडिया ब्‍यूरो' USING LATIN1);

yields these results

Some Hindi: ज़ी मीडिया ब्‍यूरो         Some Hindi: ??? ?????? ???????

The ? characters are the replacement characters.

I don't know Hindi, so I apologize if my example is nonsense in your language. — O. Jones, Sep 19 '19 at 12:32

score 0 · Answer 2 · answered Sep 20 '19 at 23:56

Supported in MySQL 8

agency_name regexp '\\p{script=Devanagari}'

Here \p introduces a class of characters meeting a character property filter. In this case, the Unicode script "Devanagari". If that doesn't cover all the characters you want to find for Hindi, you can add other Unicode scripts, blocks or ranges. (Detecting the language used in text, mi amigo, is a different animal, n'est-ce pas?)

score 0 · Answer 3 · answered Oct 04 '19 at 01:41

0

SELECT ... WHERE HEX(col) REGEXP '(..)*E0A[45]'

Will fetch rows where col contains some Devanagari characters (and possibly others).

UTF-8 hex, mostly E0A4xx has the letters; E0A5xx has the 'signs'.

answered Oct 04 '19 at 01:41

Rick James

135,179
13
127
222

Escaping Hindi Characters in mysql

3 Answers3