Announcement

Collapse
No announcement yet.

Special Characters

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

  • ScottBadger
    started a topic Special Characters

    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.

  • mayho
    replied
    Hi Scott,

    Thanks. We will reply your ticket by email.

    Leave a comment:


  • ScottBadger
    replied
    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

    Leave a comment:


  • mayho
    replied
    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.

    Leave a comment:

Working...
X