sql - 子字符串和 charindex 没有返回我所期望的
问题描述
我正在尝试仅从评论列中提取名称。但是,使用 substring 和 charindex 的组合不会返回我想要的。
以下是原始数据的样子:
MAR-05-2018 01:08:16 PM - Joe Willis (Additional comments) text blah blah
MAR-25-2018 01:25:19 PM - mitch.ward (Additional comments) text blah blah
MAR-26-2018 01:33:21 PM - Hank William (Additional comments) text blah blah
这是我的查询:
SELECT
SUBSTRING(Comment,26,CHARINDEX('(',Comment))
FROM ext.x_amr_sp
这是我的结果:
Joe Willis (Condition Action Comments
mitch.ward (Condition Action Comments
Hank William (Condition Action Comments
我想:
Joe Willis
mitch.ward
Hank William
解决方案
LEFT()
用作_
SELECT *, LEFT(Str, CHARINDEX('(', Str) - 2) --Here -2 t avoid the leading space
FROM (VALUES
('Joe Willis (Condition Action Comments'),
('mitch.ward (Condition Action Comments'),
('Hank William (Condition Action Comments')
) T(Str)
由于您更新了问题并提供了其他数据
SELECT *, SUBSTRING(Str, 27, CHARINDEX('(', Str) - 28)
FROM (VALUES
('MAR-05-2018 01:08:16 PM - Joe Willis (Additional comments) text blah blah'),
('MAR-25-2018 01:25:19 PM - mitch.ward (Additional comments) text blah blah'),
('MAR-26-2018 01:33:21 PM - Hank William (Additional comments) text blah blah')
) T(Str)
这是一个db<>fiddle看看它是如何工作的。
推荐阅读
- javascript - 2D 数组 - DS 嵌套 for 循环 javascript 不返回
- css - 混合混合模式:差异不适用于固定元素和背景图像
- android-studio - 调试 Android 应用程序错误
- angular - node_modules 包含 es6 代码不会以角度编译为 es5?
- azure - Microsoft Intune - Can you cancel a request to wipe a device?
- javascript - SVGs not rendered in Firefox 60 sometimes
- facebook - CORS:如何保护 Facebook Leadgen Webhook?
- android - RecyclerView - 删除行总是删除最后一个
- c++ - why complete string is not printing at once but when i try character by character its printing
- c - 关于指针的几处干扰