sql - 有没有办法在输出列中的特殊字符之间使用子字符串在选择查询中获取输出

Question

我正在使用选择查询并从表中输出 2 列。一列包含由特殊字符 ":" 分隔的字符串中的数据。我需要在特殊字符的第一个实例和特殊字符的第二个实例之间输出带有字符串输出的列中的数据

表 -ACC_TABLE 使用的命令：

select SC_NAME, SUBSTR(PROP,1,30)
from ACC_TABLE where PROP like '%REC%' and like PROP like '%NEC%'

前 2 行的输出——

SC_NAME  PROP

00000   {"sender":"","message":"@{source.message

1000NN  {"sender":"REC","message":"Dear Custo

2040NN  {"sender":"[e-mail]","message":"Dear Custo

我需要从列名 PROP 中获取不同且唯一的发件人，即在第一个“发件人”：“和”之间，这些值也不是特定的长度，因此我无法使用具有固定宽度的标准 substr。

select SC_NAME, SUBSTR(PROP,1,30)
from ACC_TABLE where PROP like '%REC%' and like PROP like '%NEC%'

预计会从列名 PROP 中获得不同且唯一的发件人，即在第一个“发件人”：“和”之间，这些值也不是特定的长度，因此我无法使用具有固定宽度的标准 substr。

标签： sql

Answer 1

使用以下逻辑仅获取发件人姓名/ID。

只需添加另一列并使用此逻辑来转换实际列并使用 distinct 来获取唯一的发件人

选择
reverse( substring( reverse(substring(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC" ,"message":"Dear Custo')-1),'sender',''), patindex('%[A-Za-Z0-9]%',replace(substring('{"sender":"REC ","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),'sender','' )), len(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message ":"Dear Custo')-1),'sender','')))), patindex('%[A-Za-Z0-9]%',reverse(substring(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message" :"Dear Custo')-1),'sender',''), patindex('%[A-Za-Z0-9]%',replace(substring('{"sender":"REC","message ":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),'sender','')), len (replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),'sender',''))))), len(reverse(substring(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),'发件人',''), patindex('%[A-Za-Z0-9]%',replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex( '%message%','{"sender":"REC","message":"Dear Custo')-1),'sender','')), len(replace(substring('{"sender": "REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),'sender', ''）））））））%message%','{"sender":"REC","message":"Dear Custo')-1),'sender',''), patindex('%[A-Za-Z0-9]% ',replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":" Dear Custo')-1),'sender','')), len(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message% ','{"sender":"REC","message":"Dear Custo')-1),'sender','')))))))%message%','{"sender":"REC","message":"Dear Custo')-1),'sender',''), patindex('%[A-Za-Z0-9]% ',replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":" Dear Custo')-1),'sender','')), len(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message% ','{"sender":"REC","message":"Dear Custo')-1),'sender','')))))))sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),' sender','')), len(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":" REC","message":"Dear Custo')-1),'sender','')))))))sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":"REC","message":"Dear Custo')-1),' sender','')), len(replace(substring('{"sender":"REC","message":"Dear Custo',1,patindex('%message%','{"sender":" REC","message":"Dear Custo')-1),'sender','')))))))REC","message":"Dear Custo')-1),'sender','')))))))REC","message":"Dear Custo')-1),'sender','')))))))