Better way to filter out SQL data error on large table

One of the client input'd large JSON PAYLOAD. 1000+ JSON PAYLOAD's ReceiptID field contains "NULL"/"some other word" instead of valid Blank/AlphaNumeric/Numeric.

Right now using, the following COALESCE & ISNULL based two query to narrow down for smaller subset. But what is the best quick approach to filter out these to new dirtyRowTable which would help to ask client to replay the same.

Using the following two query to find exact row that have bad data.

--Below SQL using ISNULL, returns all 1000 rows

Select top 1000 EventStoreId,
isnull(JSON_VALUE(payload,'$.ReceiptId'),0) ReceiptId
from dbo.EventStore order by 1 desc


--another SQL below using COALESCE, returns only 512 rows and error'd since 513th have value 'NULL'.
(Error: Msg 245, Level 16, State 1, Line 16
Conversion failed when converting the nvarchar value 'NULL' to data type int.)

Select top 1000 EventStoreId,
COALESCE(JSON_VALUE(payload,'$.ReceiptId'),0) ReceiptId
from dbo.EventStore order by 1 desc


1 Answer
1

COALESCE() -- although standard -- has the downfall that it evaluates the first argument twice. So when the first argument is non-trivial, ISNULL() is the better approach in SQL Server.

COALESCE()

ISNULL()

That said, the type of the ISNULL() expression is the type of the first expression. So, it is returning a string. The better way to write the code avoids implicit type conversions:

ISNULL()

ISNULL(JSON_VALUE(payload, '$.ReceiptId'), N'0') ReceiptId


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.