在sql查询中搜索

xytpbqjk 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(379)

我试图让我的查询使用where子句来查找从将特定列转换为varbinary和varchar的查询返回的信息。我必须施法，因为原始数据是十六进制的。
我的问题：

SELECT ScriptID, RowOrder, cast(cast(ScriptData as varbinary(max)) as varchar(max)) AS Converted_SD
FROM Script_Data

典型的回报是这样的：

--------------------------------------------------------------------------------------------
| ScriptID | RowOrder | Converted_SD                                                       |
--------------------------------------------------------------------------------------------
| 5006     | 1        | CVisScript {331 1 76 "\"1007\"" "Call.PeripheralVariable10" 284 "" |
| 5007     | 1        | CVisScript {332 1 76 "\"2007\"" "Call.PeripheralVariable10" 285 "" |
| 5009     | 1        | CVisScript {333 1 76 "\"1037\"" "Call.PeripheralVariable10" 286 "" |
| 5014     | 1        | CVisScript {334 1 76 "\"1407\"" "Call.PeripheralVariable10" 287 "" |
| 5015     | 1        | CVisScript {335 1 76 "\"1068\"" "Call.PeripheralVariable10" 288 "" |
| 5016     | 1        | CVisScript {336 1 76 "\"6578\"" "Call.PeripheralVariable10" 289 "" |
--------------------------------------------------------------------------------------------

我希望能够返回scriptid、roworder，但只返回call.peripheraldvariable10和它前面的四位数字-在converted\u sd列中没有其他内容。我试过这么多子查询，但似乎无法组合出一个有效的子查询。所以我设想它看起来像这样：

--------------------------------------------------------
| ScriptID | RowOrder | Converted_SD                   |
--------------------------------------------------------
| 5006     | 1        | 1007 Call.PeripheralVariable10 |
| 5007     | 1        | 2007 Call.PeripheralVariable10 |
| 5009     | 1        | 1037 Call.PeripheralVariable10 |
| 5014     | 1        | 1407 Call.PeripheralVariable10 |
| 5015     | 1        | 1068 Call.PeripheralVariable10 |
| 5016     | 1        | 6578 Call.PeripheralVariable10 |
--------------------------------------------------------

谢谢您

sql sql-server subquery varchar varbinary

来源：https://stackoverflow.com/questions/61881259/searching-within-a-sql-query

1条答案

按热度按时间

bjg7j2ky1#

如果您使用的是sql server 2016，则可以使用string\u split函数。
你可以在这里看到它的用法(https://www.mssqltips.com/sqlservertip/4884/sql-server-2016-stringsplit-function/)
如果您知道该格式将始终使用双引号调用，则可以在双引号中使用该函数，或者如果有多个格式使用replace，则使用相同的分隔符。

with cte as  (
select 5006 as ScriptID,  1 RowOrder, 'CVisScript {331 1 76 "\"1007\"" "Call.PeripheralVariable10" 284 ""' as Con_SD union all 
select 5007 as ScriptID,  1 RowOrder, 'CVisScript {332 1 76 "\"2007\"" "Call.PeripheralVariable10" 285 ""' as Con_SD union all 
select 5009 as ScriptID,  1 RowOrder, 'CVisScript {333 1 76 "\"1037\"" "Call.PeripheralVariable10" 286 ""' as Con_SD union all 
select 5014 as ScriptID,  1 RowOrder, 'CVisScript {334 1 76 "\"1407\"" "Call.PeripheralVariable10" 287 ""' as Con_SD union all 
select 5015 as ScriptID,  1 RowOrder, 'CVisScript {335 1 76 "\"1068\"" "Call.PeripheralVariable10" 288 ""' as Con_SD union all 
select 5016 as ScriptID,  1 RowOrder, 'CVisScript {336 1 76 "\"6578\"" "Call.PeripheralVariable10" 289   ' as Con_SD )

select c.ScriptID, c.RowOrder, tbl.n   from cte c  
cross apply  (select * from  string_split (con_SD, '"'))   tbl(n)
where tbl.n like '%call%';

输出：

赞(0）回复(0）举报 2021-08-09

我来回答

在sql查询中搜索

1条答案

相关问题

热门标签

最新问答