question

GrahamRock-8444 avatar image
0 Votes"
GrahamRock-8444 asked GrahamRock-8444 commented

SQL Server How To query Json field

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is a Json example.

That's everything else but not valid JSON, see https://en.wikipedia.org/wiki/JSON#Syntax
Therefore you can not use the build-in JSON functions of SQL Server.

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered GrahamRock-8444 commented

Hi @GrahamRock-8444,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's great, Thank you for your help.

0 Votes 0 ·