请教SQL查询json字符串
FID JSON
1 [{"city": "北京", "name": "张三", "tel": "123"}]
怎么查询
如何写一个数据表函数三个参数 func(key,value,oper)
我调用这个函数传入 func('name','张三','like') 就能得到该条记录。。
SQL JSON
[解决办法]
if OBJECT_ID('tablename', 'u') is not null drop table #temp;
CREATE TABLE tablename(fid int, json nvarchar(MAX))
insert tablename
select '1','[{"city": "北京", "name": "张三", "tel": "123"}]'
go
CREATE FUNCTION func(@field nvarchar(255), @value nvarchar(MAX), @oper varchar(10))
RETURNS @temp table(fid INT, json NVARCHAR(max))
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
IF(@oper = 'like')
BEGIN
INSERT @temp
select fid, json from tablename where json LIKE '%'+ '"' + @field + '": "' + @value + '"' +'%'
end
RETURN
end
GO
SELECT * FROM dbo.func('name','张三','like')
/*
fidjson
1[{"city": "北京", "name": "张三", "tel": "123"}]
*/ALTER FUNCTION func(@field nvarchar(255), @value nvarchar(MAX), @oper varchar(10))
RETURNS @temp table(fid INT, json NVARCHAR(max))
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
IF(@oper = 'like')
BEGIN
INSERT @temp
select fid, json from tablename
where CHARINDEX('"' + @field + '": "' + @value + '"', json) > 0
end
RETURN
end
GO