如何在SQL中组合子字符串和修剪

Question

我试图提取两个下划线字符之间的数据。在某些情况下，第二个下划线可能不存在。

MyFldP_36840U_216137C_203134_H

我试过了:

substring(i.[MyFld],
CHARINDEX ('_',i.[MyFld])+1,len(i.[MyFld])
-CHARINDEX ('_',i.[MyFld])
) [DerivedPrimaryKey]

我得到这个:

DerivedPrimaryKey36840216137203134 _h203134 _w

https://dbfiddle.uk/uPKC6oX4

我想删除第二个下划线和它后面的数据。我想把它和一个修剪的权利结合起来，但我不确定从哪里开始。

我该怎么做?

Answer 1

我们可以从简化到目前为止的内容开始。我还将添加足够的内容使其成为一个完整的查询，以便我们可以在后续步骤的上下文中看到它:

SELECT 
right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [DerivedPrimaryKey]
FROM I

完成这些后，我们现在可以使用它作为删除字段末尾部分的源:

SELECT 
reverse(substring(reverse(step1)
, charindex('_', reverse(step1))+1
, len(step1)
)) [DerivedPrimaryKey]
FROM (
SELECT right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)) [step1]
FROM I
) T

注意嵌套层。当然，您可以删除嵌套，但这意味着每次看到step1时都要复制整个内部表达式(我花时间简化它是件好事):

SELECT 
reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
, charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld))))+1
, len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
))
FROM I

现在回到表达式:

reverse(substring(reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
, charindex('_', reverse(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld))))+1
, len(right(i.MyFld, len(i.MyFld) - charindex('_', i.MyFld)))
))

看到它在这里工作:

https://dbfiddle.uk/nFO4Vwhm

还有这样一个替代表达式，可以节省一次函数调用:

left(   right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)), 
coalesce(
nullif(
charindex('_', 
right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) 
) -1, -1,
), 
len( right(i.MyFld,len(i.MyFld)-charindex('_',i.MyFld)) )
)
)

Answer 2

还有两个选项。如果您的数据不超过4段，则使用parsename()。第二个使用JSON数组

Declare @YourTable Table ([MyFld] varchar(50))  Insert Into @YourTable Values 
('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')

Select *
,UsingParseName = reverse(parsename(reverse(replace(MyFld,'_','.')),2))
,UsingJSONValue = json_value('["'+replace(MyFld,'_','","')+'"]','$[1]')
From  @You

结果

MyFld       UsingParseName  UsingJSONValue
P_36840     36840           36840
U_216137    216137          216137
C_203134_H  203134          203134
C_203134_W  203134          203134

Answer 3

可以这样做:

Declare @testData Table ([MyFld] varchar(50));
Insert Into @testData (MyFld)
Values ('P_36840')
, ('U_216137')
, ('C_203134_H')
, ('C_203134_W');

Select *
, second_element = substring(v.MyFld, p1.pos, p2.pos - p1.pos - 1)
From @testData                                               As td
Cross Apply (Values (concat(td.MyFld, '__')))                 As v(MyFld)  -- Make sure we have at least 2 delimiters
Cross Apply (Values (charindex('_', v.MyFld, 1) + 1))         As p1(pos)   -- First Position
Cross Apply (Values (charindex('_', v.MyFld, p1.pos) + 1))    As p2(pos)   -- Second Position

如果在第一个元素中有固定数量的字符，那么它可以简化为:

Select *
, second_element = substring(v.MyFld, 3, charindex('_', v.MyFld, 4) - 3)
From @testData td
Cross Apply (Values (concat(td.MyFld, '_'))) As v(MyFld)

Answer 4

如果期望的字符并不总是存在并且我不需要结果值，我通常会尝试伪造SQL:

SELECT SUBSTRING(field_Calculated, 1, CHARINDEX('_', field_Calculated) - 1)  
FROM (SELECT SUBSTRING(MyFld, CHARINDEX('_', MyFld) + 1, LEN(MyFld)) + '_' As field_Calculated 
FROM MyTable) T

我认为这很清楚，但我真的很喜欢ParseName的解决方案@JohnCappalletti建议。

如果只有一个数字值，可以使用string_split:

SELECT * FROM MyTable
CROSS APPLY string_split(MyFld, '_')
WHERE ISNUMERIC(value) = 1

无论哪种方式，你都必须在决定最佳方法之前仔细查看数据。

Answer 5

您的数据

Declare @Table Table ([MyFld] varchar(100))  
Insert Into @Table
([MyFld] ) Values 
('P_36840')
,('U_216137')
,('C_203134_H')
,('C_203134_W')

使用SubString,Left和PatIndex

select 
Left(
SubString(
[MyFld], 
PatIndex('%[0-9.-]%', [MyFld]), 
8000
), 
PatIndex(
'%[^0-9.-]%', 
SubString(
[MyFld], 
PatIndex('%[0-9.-]%', [MyFld]), 
8000
) + 'X'
)-1
)  as DerivedPrimaryKey
from 
@Table