SQL Server 使用T-SQL或C#,如何确定字符串中所有子字符串的位置和长度?

hivapdat  于 2022-12-17  发布在  C#
关注(0)|答案(3)|浏览(255)

给定以下字符串(忽略双引号):
"I101G2.2 OZ 001 0002200 L Y 0010000 "
我需要找到每个子字符串的起始位置和长度。例如,使用上面的字符串,第一个子字符串是I101G2.2,从位置0开始,长度为8个字符。请记住,每个空格字符(““)都应被视为一个子字符串,长度始终为一个字符。因此,输出应该如下所示:

**Sub-String Value**,**Starting Position**, **Length**
I101G2.2,0,8
{space},9,1
OZ,10,2
{space},12,1
{space},13,1
001,14,3
{space},17,1
0002200,18,7
{space},25,1
L,26,1
{space},27,1
{space},28,1
{space},29,1
{space},30,1
{space},31,1
{space},32,1
{space},33,1
{space},34,1
Y,35,1
{space},36,1
{space},37,1
{space},38,1
{space},39,1
{space},40,1
{space},41,1
{space},42,1
{space},43,1
0010000,44,7

等等。
我们有一个用Cobol编写的生产系统,它以上述字符串格式输出信息。我有一个表,它将位置和字符串长度Map到另一个表中的列。因此,我们的想法是获得字符串的位置和长度,并与Map表进行比较,以确定它属于表中的哪个列。例如,0002200是item类,因为它位于位置18。并且长度为7个字符。
先谢了!

6ojccjat

6ojccjat1#

我很惊讶你还在用Cobol,我妈妈在80年代用Cobol写代码!
下面是我的尝试(它处理所有空格,甚至是字符串末尾的空格):

IF object_id('tempdb..#t') IS NOT NULL
    DROP TABLE #t
GO
CREATE TABLE #t (str VARCHAR(100))

INSERT INTO #t
VALUES ('I101G2.2 OZ      001 0002200 L Y 0010000')

;WITH cte AS(
    SELECT  cast(CASE WHEN charindex(' ', t.str) = 1 THEN ' ' ELSE LEFT(str, charindex(' ', str)) END AS varchar(100)) str
    ,   0 AS startPosition
    ,   CASE WHEN charindex(' ', t.str) = 1 THEN 1 ELSE charindex(' ', t.str) END AS nextIndex
    ,   stuff(str, 1,charindex(' ', t.str)-1, '') AS strLeft 
    FROM    #t t
--  
    UNION ALL
    SELECT  cast(CASE WHEN charindex(' ', t.strLeft) = 1 THEN ' ' WHEN charindex(' ', t.strleft) = 0 THEN strLeft ELSE LEFT(strLeft, charindex(' ', strLeft)) END AS varchar(100))
    ,   startPosition + nextIndex
    ,   CASE WHEN charindex(' ', t.strLeft) = 1 THEN 1 ELSE charindex(' ', t.strLeft) END
    ,   stuff(strLeft, 1,charindex(' ', t.strLeft) + CASE WHEN charindex(' ', t.strleft) <> 1 THEN -1 ELSE 0 END, '')
    FROM    CTE t
    WHERE   datalength(strLeft) > 0
    )
SELECT  str, startPosition, datalength(str) AS length
FROM    cte
OPTION(maxrecursion 0);
tv6aics1

tv6aics12#

要在c#中实现这一点,你可以简单地在一个循环中遍历字符串,当我们遇到非空格字符时,我们可以把它们附加到一个StringBuilder中,当我们遇到空格时,我们首先把到目前为止捕获到的字符,沿着它们的长度和起始位置,添加到一个字符串列表中(每个代表一个子字符串),清除下一个子字符串的StringBuilder,然后添加空格字符及其位置和长度。在循环结束时,如果我们的StringBuilder中有一个子字符串,将其添加到列表,然后返回列表。
原始示例中的一个错误是第一个子字符串从位置0开始,并且包含8个字符,这意味着下一个子字符串应该从位置8开始,但您的示例显示它从9开始。
这里有一个方法可以做到这一点:

public static List<string> GetParts(string input)
{
    if (input == null) return null;
    var result = new List<string>();
    if (input.Length == 0) return result;

    var currentPart = new StringBuilder();

    for (int i = 0; i < input.Length; i++)
    {
        if (input[i] == ' ')
        {
            if (currentPart.Length > 0)
            {
                var part = currentPart.ToString();
                result.Add($"{part},{i - part.Length},{part.Length}");
                currentPart.Clear();
            }

            result.Add($"{{space}},{i},1");
        }
        else
        {
            currentPart.Append(input[i]);
        }
    }

    if (currentPart.Length > 0)
    {
        var part = currentPart.ToString();
        result.Add($"{part},{input.Length - part.Length - 1},{part.Length}");
    }

    return result;
}

一个例子称之为:

static void Main(string[] args)
{
    var input = 
        "I101G2.2 OZ  001 0002200 L       Y        0010000                     ";
        
    var parts = GetParts(input);
    parts.ForEach(Console.WriteLine);

    Console.Write("\n\nDone. Press any key to exit...");
    Console.ReadKey();
}
fwzugrvs

fwzugrvs3#

我不确定您在这里的确切要求,但这是一个相当标准的序数类型的字符串拆分。
给予这个:

SET ANSI_PADDING ON

DECLARE @ThatsALongOne NVARCHAR(MAX) = 'I101G2.2 OZ  001 0002200 L       Y        0010000                     ';
DECLARE @WhaddaYaWant NVARCHAR(10) = ' ';

SET @ThatsALongOne+='.'
;WITH cte AS 
(
SELECT @ThatsALongOne  AS TheWholeSheBang, 
CHARINDEX(@WhaddaYaWant,@ThatsALongOne)-1  AS CI, LEN(@ThatsALongOne)  AS LEN, 
CASE WHEN LEFT(LEFT(@ThatsALongOne ,CHARINDEX(@WhaddaYaWant,@ThatsALongOne)),1) = @WhaddaYaWant THEN @WhaddaYaWant ELSE LEFT(@ThatsALongOne ,CHARINDEX(@WhaddaYaWant,@ThatsALongOne)) END AS This, 
CASE WHEN LEFT(LEFT(@ThatsALongOne ,CHARINDEX(@WhaddaYaWant,@ThatsALongOne)),1) = @WhaddaYaWant THEN RIGHT(@ThatsALongOne, LEN(@ThatsALongOne) -CHARINDEX(@WhaddaYaWant,@ThatsALongOne )) ELSE RIGHT(@ThatsALongOne, 1+ LEN(@ThatsALongOne) -CHARINDEX(@WhaddaYaWant,@ThatsALongOne )) END AS WhatsLeft
, 1 AS Incidence
, CAST(0 AS BIGINT) AS Start
UNION ALL                                                                                                                                                                                                   
SELECT @ThatsALongOne  AS TheWholeSheBang, 
CASE WHEN LEFT(LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft)),1) = @WhaddaYaWant THEN 1 ELSE LEN(LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft))) END     AS CI, 
LEN(WhatsLeft)       AS LEN, 
CASE WHEN LEFT(LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft)),1) = @WhaddaYaWant THEN @WhaddaYaWant ELSE LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft)) END AS This, 
CASE WHEN LEFT(LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft)),1) = @WhaddaYaWant THEN RIGHT(@ThatsALongOne, LEN(WhatsLeft) -CHARINDEX(@WhaddaYaWant,WhatsLeft )) ELSE RIGHT(WhatsLeft, 1+ LEN(WhatsLeft) -CHARINDEX(@WhaddaYaWant,WhatsLeft )) END AS WhatsLeft
, Incidence + 1 AS Incidence
, CASE WHEN LEFT(LEFT(WhatsLeft ,CHARINDEX(@WhaddaYaWant,WhatsLeft)),1) = @WhaddaYaWant THEN 1 ELSE CHARINDEX(@WhaddaYaWant,WhatsLeft) END
FROM cte
 WHERE WhatsLeft <> '.'
)

SELECT This AS SubString, SUM(CI) OVER (PARTITION BY cte.TheWholeSheBang ORDER BY cte.Incidence)-CI AS StartingPosition, CI AS Length
  FROM cte
SubString   StartingPosition    Length
I101G2.2    0                   8
            8                   1
OZ          9                   2
            11                  1
            12                  1
001         13                  3
..

相关问题