时间戳

首页 > 解决方案 > 字符串操作/正则表达式

sql - 字符串操作/正则表达式

问题描述

我有一个NVARCHAR(MAX)存储日志流的列。我需要的是在 select 语句级别提取一些值。由于我无法在没有 CLR 的情况下应用正则表达式，因此使用一些“字符串操作”函数的解决方案是什么？

例如，我可能在Log列中有这个值：

$CF_NONPROD_USERNAME -p $CF_NONPROD_PASSWORD -o $ORG -s $SPACE\u001B[0;m\nAPI endpoint: https://api.server02.pcf.com/\nAuthenticating...\nOK\n\nTargeted org Order-PSOrg\n\nTargeted space TEST4\n\n\n\nAPI endpoint:   https://api.server02.pcf.com (API version: 2.131.0)\nUser:           myuser@user.from.ldap.cf\nOrg:            Order-PSOrg\nSpace:          TEST4\n\u001B[32;1m$ $PRE_DEPLOY_CMD\u001B[0;m\n\"Deployment started\"\n\u001B[32;1m$ cf push $APP_NAME -p $TARGET -f $PCF_MF_FILE --no-start\u001B[0;m\nPushing f

我需要提取：

Targeted Org = *in this sample would be "Order-PSOrg"*

Targeted Space = *in this sample would be "TEST4"*

Api Endpoint = *in this sample would be "https://api.server02.pcf.com"*

有什么建议么？

谢谢，

标签： sqlsql-server

解决方案

这个查询非常丑陋，但可以完成工作。基本上，您从要查找的键的位置（“Targeted Org”、“Targeted Space”和“Api Endpoint”）检索子字符串，直到下一个“\n”字符串的位置。最后，您从先前的结果中删除该键。

使用的功能：

  1. charindex获取字符串中子字符串的位置
  2. substring获取两个位置之间的子字符串
  3. replace从上一个结果中删除搜索到的键
  4. ltrim和rtrim删除开头和结尾的空格

询问：

create table MyLogsTable (Log varchar(max))    
insert into MyLogsTable (Log) values ('$CF_NONPROD_USERNAME -p $CF_NONPROD_PASSWORD -o $ORG -s $SPACE\u001B[0;m\nAPI endpoint: https://api.server02.pcf.com/\nAuthenticating...\nOK\n\nTargeted org Order-PSOrg\n\nTargeted space TEST4\n\n\n\nAPI endpoint: https://api.server02.pcf.com (API version: 2.131.0)\nUser: myuser@user.from.ldap.cf\nOrg: Order-PSOrg\nSpace: TEST4\n\u001B[32;1m$ $PRE_DEPLOY_CMD\u001B[0;m\n\"Deployment started\"\n\u001B[32;1m$ cf push $APP_NAME -p $TARGET -f $PCF_MF_FILE --no-start\u001B[0;m\nPushing f')

select ltrim(rtrim(replace(substring(Log, charindex('Targeted Org', Log), charindex('\n', Log, charindex('Targeted Org', Log)) - charindex('Targeted Org', Log)), 'Targeted Org', ''))) as Target_Org,
       ltrim(rtrim(replace(substring(Log, charindex('Targeted Space', Log), charindex('\n', Log, charindex('Targeted Space', Log)) - charindex('Targeted Space', Log)), 'Targeted Space', ''))) as Target_Space,
       ltrim(rtrim(replace(substring(Log, charindex('Api Endpoint:', Log), charindex('\n', Log, charindex('Api Endpoint:', Log)) - charindex('Api Endpoint:', Log)), 'Api Endpoint:', ''))) as Api_Endpoint       
from MyLogsTable

你可以看到它在 DBFiddle

推荐阅读