Question 113356 in Mysql Question posted in
The official MySQL documentation can be found here.

Get last three chars of a json element in mySQL column

I have json data in a column called settings:

{"AreaAdmin":null,
"StatsAreaAdmin":null,
"Logo":"1664805113094.svg",
"ExportFree":null,
"Banner":null,
"BannerMobile":null,
"BannerRetina":null,
"BannerAddr": null
}

I have figured out how to grab a json element:

SELECT *
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

How do I grab the last three chars of an element? I want to check if all Logo are svg or not. I’ve tried

SELECT SUBSTRING(JSON_EXTRACT(settings,"$.Logo"), LEN(JSON_EXTRACT(settings,"$.Logo")-1,3))
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

But get
Query 1 ERROR: FUNCTION uc_common.LEN does not exist

Doing

SELECT RIGHT(JSON_EXTRACT(settings,"$.HQLogo"),3)

gives me vg"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I got it using

    SELECT SUBSTRING(JSON_EXTRACT(settings,"$.Logo"),-4,3 )
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

  2. 0

    You can try and test this:

    SELECT RIGHT(JSON_UNQUOTE(JSON_EXTRACT(settings,'$.Logo')),3) FROM user;


+--------------------------------------------------------+
| RIGHT(JSON_UNQUOTE(JSON_EXTRACT(settings,'$.Logo')),3) |
+--------------------------------------------------------+
| svg                                                    |
+--------------------------------------------------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search