如何从单个字符串中提取特定的子字符串？

I have a string that I need to break apart for codes.

我有一个字符串,我需要拆分代码。

String examples - 'H345678_30', 'q789038_155'

字符串示例 - 'H345678_30','q789038_155'

To extract the code after the underscore I have something that works

要在下划线后提取代码我有一些有用的东西

  Select Code = RIGHT(@temp ,CHARINDEX('_',REVERSE(@temp)) - 1)

The code I'm having a hard time extracting is from the first part before the '_'. Lets say we take 'H345678_30', the first 3 characters are always Precodes like H34 or in the other example as mentioned aboved it will be Q78. The #s that follow after H34 and end before '_' is what I need.

我提取困难的代码来自'_'之前的第一部分。假设我们采用'H345678_30',前三个字符总是像H34那样的Precodes,或者在上面提到的另一个例子中它将是Q78。在H34之后和在'_'之前结束的#是我需要的。

In this case it will be 5678.

在这种情况下,它将是5678。

So far I have this but any suggestions to make this work more efficiently it will be great

到目前为止,我有这个,但任何建议,使这项工作更有效,这将是伟大的

Select TCode = LEFT((SUBSTRING(@temp,Charindex('q',@temp)+3,Len(@temp))), (charindex('_', ((SUBSTRING(@temp,Charindex('q',@temp)+3,Len(@temp))))))-1)

Thank you

2 个解决方案

#1

If the first 3 characters are always the "precode" then you can simply do this:

如果前3个字符始终是“precode”,那么您可以简单地执行此操作:

SELECT SUBSTRING('H345678_30',4,CHARINDEX('_','H345678_30')-4)

#2

You can try following code, I have captured everything that is on left side of '_' character and then I have removed first 3 characters to get 5678:

您可以尝试以下代码,我已经捕获了“_”字符左侧的所有内容,然后我删除了前3个字符以获得5678:

DECLARE @x VARCHAR(max) = 'H345678_30'
SELECT SUBSTRING(LEFT(@x, CHARINDEX('_', @x)-1), 4, 10)

#1