To validate JSON, you can use the ISJSON function. This is a scalar function and checks whether the input string is valid JSON data. The function has one input argument:
- string: This is an expression of any string data type, except text and ntext.
The return type of the function is int, but only three values are possible:
- 1 , if the input string is JSON conforming
- 0 , if the input string is not valid JSON data
- NULL , if the input expression is NULL
The following statement checks whether the input variable is JSON valid:
SELECT ISJSON ('test'), ISJSON (''), ISJSON ('{}'), ISJSON ('{"a"}'), ISJSON ('{"a":1}'), ISJSON ('{"a":1"}');
Here is the output:
------ ------ ------ ------ ------ ------ 0 0 1 0...