SQL Server How To query Json field

Question

Hello
I have a field in my SQL Server database that is Json, how do I write a query to only get marketing consent as yes.

This is a Json example.
[Type]Contractual[Categories]EMAIL=Yes;Post=Yes;TEXT=Yes;Phone=Yes;[/Categories][/Type]<br/>[Type]Marketing[Categories]EMAIL=Yes;Post=Yes;TEXT=Yes;Phone=Yes;[/Categories][/Type]<br/>

Thank you for reading;

Regards

Graham

Answer 1

Hi @Graham Rock ,

Welcome to Microsoft Q&A!

Please help check the correct format of your json example. It does not look like a JSON or XML.

You could also refer below and check whether it is helpful to you.

DECLARE @Tbl TABLE ( col varchar(max))  
  
insert into @Tbl values  
('[Type]Contractual[Categories]EMAIL=Yes;Post=Yes;TEXT=Yes;Phone=Yes;[/Categories][/Type]<br/>[Type]Marketing[Categories]EMAIL=Yes;Post=Yes;TEXT=Yes;Phone=Yes;[/Categories][/Type]<br/>')  
  
SELECT SUBSTRING(col,CHARINDEX('Marketing[Categories]',col)+len('Marketing[Categories]'),CHARINDEX('[/Categories]',col,CHARINDEX('Marketing[Categories]',col))-(CHARINDEX('Marketing[Categories]',col)+len('Marketing[Categories]')))  
from @Tbl  

Output:

EMAIL=Yes;Post=Yes;TEXT=Yes;Phone=Yes;  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.