using the charindex to match a field to a search

Question

I have a query where one of the columns value are coming a json formatted value as:

column1 {"type":"a","main":"/documents/500/a.pdf","id":500}

i want to use the id in the where clause to search like

where column1 = 500

i want to use the 500 on the left hand side for matching to a search

Please tag with the version of SQL Server you are using. Native JSON support was added in SQL Server 2016. If you need to parse JSON and are using an older version, this may be a good reason to upgrade. — Gordon Linoff, May 27 '19 at 13:06
`SELECT * FROM .. WHERE JSON_VALUE(column1, '$.id') = 500`. Leave `CHARINDEX`/`LIKE` at home for structured data like this. Note that you may want to [index the data](https://learn.microsoft.com/sql/relational-databases/json/index-json-data) if you want this search to be efficient. — Jeroen Mostert, May 27 '19 at 13:11
On SQL Server 2016, it does work (`SELECT * FROM (VALUES ('{"type":"a","main":"/documents/500/a.pdf","id":500}"')) t(column1) WHERE JSON_VALUE(column1, '$.id') = 500`). You'll have to be more specific about your table structure/JSON otherwise. (There is no "version 12.1" of SQL Server; you may be confusing this with the version of Management Studio. There are 12.0.x versions of SQL Server, and those are SQL Server 2014. Use `SELECT @@VERSION` to get the server version.) — Jeroen Mostert, May 27 '19 at 14:33
Possible duplicate of [Query JSON inside SQL Server 2012 column](https://stackoverflow.com/questions/23723473/query-json-inside-sql-server-2012-column) — SMor, May 27 '19 at 17:49

score 0 · Accepted Answer · answered May 29 '19 at 07:16

0

You can try this:

Declare @t table ( EMpid int , Groups nvarchar(500))

Insert into @t values (500,'{"type":"a","main":"/documents/500/a.pdf","id":500}')

select * from @t where replace(RIGHT(groups, CHARINDEX(':', REVERSE(groups)) -1),'}','') = 500

answered May 29 '19 at 07:16

Red Devil

2,343
2
21
41

using the charindex to match a field to a search

1 Answers1