Announcement

Collapse
No announcement yet.

Special Characters

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Special Characters

    Because MySQL 5.6 doesn't include JSON functions, I'm using the SUSTRING_INDEX function to parse the JSON strings in the product_options field like this:

    If(LOCATE('Phone Number',product_options) <> 0, SUBSTRING_INDEX(SUBSTRING_INDEX(product_options, '"Phone Number","value":"', -1), '","print_value"', 1), null) AS pet_phone

    The problem I'm having is that special characters are showing as unicode characters in the results (i.e. "&#039;" instead of an apostrophe) . For the product_options field, the Character Set is set to utf8 and Collation is set to utf8_general_ci. For the database, Encoding is set to Auto.

    I don't have this issue with other text fields like the shipping street address.

  • #2
    Hi ScottBadger,

    Please provide the following information for investigation. You can send the information to https://help.navicat.com/hc/en-us/requests/new

    1. Your Navicat version, e.g. Navicat for MySQL (Windows) 12.0.29
    2. Which feature did you use? e.g. Query
    3. The sql file of your table. Sample data will be find as long as it can reproduce the problem. To dump the table, right-click it on the Objects tab and select Dump SQL File > Structure And Data.
    4. The whole query you ran.
    5. Screenshots showing the problem.

    Comment


    • #3
      Hi Mayho,

      I've sent the information you requested, but I did not attach the sql files of the tables being used. I'm seeing the special character issue with the same query/report as the Blank Columns issue I posted about separately in the forum and I put the tables in a Dropbox folder.

      I'm seeing the issue with the results from both the Query and the Report that the query is part of. Here's the query:

      SELECT
      DATE(mage_sales_order_item.created_at) as order_date,
      TRIM(LEADING '0' FROM mage_sales_order.increment_id) AS order_number,
      mage_sales_order.status,
      IF(mage_sales_order_address.lastname IS NULL, mage_sales_order_address.firstname, CONCAT(mage_sales_order_address.firstname, ' ', mage_sales_order_address.lastname)) AS full_name,
      mage_sales_order_address.company,
      mage_sales_order_address.street,
      mage_sales_order_address.city,
      mage_sales_order_address.region,
      mage_sales_order_address.postcode,
      mage_sales_order_item.sku,
      mage_sales_order_item.name,
      TRUNCATE(mage_sales_order_item.qty_ordered, 0) AS qty,
      LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(product_optio ns, '"label":"Size","value":"', -1), '","option_id"', 1), 5) AS size,
      LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(product_optio ns, '"Tag Style","value":"', -1), '","option_id"', 1), 7) AS style,
      SUBSTRING_INDEX(SUBSTRING_INDEX(product_options, '"Pet Name","value":"', -1), '","print_value"', 1) AS pet_name,
      If(LOCATE('Phone Number',product_options) &lt;&gt; 0, SUBSTRING_INDEX(SUBSTRING_INDEX(product_options, '"Phone Number","value":"', -1), '","print_value"', 1), null) AS pet_phone,
      If(LOCATE('Line 1',product_options) &lt;&gt; 0, SUBSTRING_INDEX(SUBSTRING_INDEX(product_options, '"Line 1","value":"', -1), '","print_value"', 1), null) AS Line_1,
      If(LOCATE('Line 2',product_options) &lt;&gt; 0, SUBSTRING_INDEX(SUBSTRING_INDEX(product_options, '"Line 2","value":"', -1), '","print_value"', 1), null) AS Line_2
      FROM
      mage_sales_order
      INNER JOIN mage_sales_order_item ON mage_sales_order_item.order_id = mage_sales_order.entity_id
      INNER JOIN mage_sales_order_address ON mage_sales_order_address.parent_id = mage_sales_order.entity_id
      WHERE
      DATE(mage_sales_order_item.created_at) BETWEEN [$from_date] AND [$to_date] AND
      (mage_sales_order_item.sku LIKE 'DTA-%' OR mage_sales_order_item.sku LIKE 'SDTA-%') AND
      mage_sales_order_item.product_type = 'configurable' AND
      mage_sales_order_address.address_type = 'shipping'
      ORDER BY
      mage_sales_order.increment_id

      Cheers,
      Scott

      Comment


      • #4
        Hi Scott,

        Thanks. We will reply your ticket by email.

        Comment

        Working...
        X