In this blog post, let’s learn about the error message “13607 – JSON path is not properly formatted. Unexpected character ‘%lc’ is found at position %d.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
13607 – JSON path is not properly formatted. Unexpected character ‘%lc’ is found at position %d.
Reason for the Error
Let’s say we have an EmailAddress table in the AdventureWorks2019 database with a column named EmailAddress that stores JSON data. We want to retrieve the value of the “Email” property from the JSON objects in the EmailAddress column:
SELECT JSON_VALUE([EmailAddress], '$.Email') AS EmailAddress FROM Person.EmailAddress;
Assuming the JSON data in the EmailAddress column is formatted correctly, the above query should execute successfully and return the value of the “Email” property for each JSON object.
Let’s say we accidentally include an invalid character in the JSON path. For example, we might accidentally have a comma (“,”) instead of a period (. “”):
SELECT JSON_VALUE([EmailAddress], '$,Email') AS EmailAddress FROM Person.EmailAddress;
This query will result in the following error message:
Solution
The error message indicates that the JSON path is not formatted correctly and that an unexpected comma was found at position 1. To fix the issue, we need to correct the JSON path so that it uses the proper syntax. In this case, we need to replace the comma with a period:
