SQL提取子 [英] SQL Extract substring

问题描述

我有一个问题，在提取SQL查询结果的子字符串。

I'm having an issue extracting a substring in SQL query results.

下面的情况：我有一个包含以下格式的ax123456uhba，ax54232hrg，ax274895rt，ax938477ed1，ax73662633wnn2

Here's the situation: I have a column that contains strings in the following format "ax123456uhba", "ax54232hrg", "ax274895rt", "ax938477ed1", "ax73662633wnn2"

我需要提取的数字字符串，它是preceded其次为信。然而，偶尔有一个数字，我并不需要尾随字符串。尾随字符的长度也不是一成不变的，所以我不能只是做一个简单的子功能。

I need to extract the numerical string that is preceded and followed by letters. However, occasionally there is a number in the trailing string that I don't need. The length of the trailing characters is not static so I can't just do a simple substring function.

我不一定要求完成code，只是一个有益的推动方向是正确的，如果可能的。

I'm not necessarily asking for completed code, just a helpful push in the right direction if possible.

在此先感谢您的帮助。

推荐答案

看起来PATINDEX是你所需要的。

It looks like PATINDEX is what you need.

返回字符串中发现了一个模式的第一个指标 - 预计经常EX pression 看到这一点 - > <一个href="http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/" rel="nofollow">http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

Returns the first index of a pattern found in a string - expects regular expression see this -> http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

这里的code在这里复制到去掉字母数字字符的字符串 - 它不应该花费太长的时间来改变这种剥离出第一连续序列的数字字符串中的

Here's the code copied here to strip out alphanumeric characters from a string - it shouldn't take too long to change this to strip out first contiguous series of digits from a string.

CREATE FUNCTION dbo.UDF_ParseAlphaChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @IncorrectCharLoc SMALLINT
    SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

    WHILE @IncorrectCharLoc > 0
    BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
    END

    SET @string = @string

    RETURN @string
END
GO

这篇关于SQL提取子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！