Блог

У меня есть поле базы данных, которое является VARCHAR с данными, хранящимися следующим образом:

 DatabaseField
1185731-2148838B
1185731-2148838S
1185731-2148838W
1185731-2148839B
 

Я хотел бы разделить это поле на два отдельных столбца, содержащих числовые цифры по обе стороны от «-«.

Чтобы получить значения до и после ‘-‘, я могу использовать следующее

 SUBSTRING(DatabaseField,0,CHARINDEX('-',DatabaseField))
SUBSTRING(DatabaseField,CHARINDEX('-',DatabaseField) 1,LEN(DatabaseField))
 

Что дает мне

 NewColumnA   NewColumnB
1185731      2148838B
1185731      2148838S
1185731      2148838W
1185731      2148839B
 

Как я мог бы затем удалить любые нечисловые символы из нового столбца B — и это может быть не одна буква в столбце — это может быть 2 или 3, поэтому я не могу просто удалить последнюю цифру из нового столбца.

Набейте их!

 declare @test table (DatabaseField varchar(30));

insert into @test values
  ('1185731-2148838B') 
, ('1234567-1234567?') 
, ('1185731-214883612WAH') 
, ('1185731-2148839BLAH') 
, ('AYE-CARAMBA')
, ('Stufffffff') 
;

select DatabaseField
, [NewColumnA] = NULLIF(STUFF(DatabaseField,PATINDEX('%[0-9]-%',Databasefield),42,''),'') 
, [NewColumnB] = PARSENAME(REPLACE(STUFF(DatabaseField,PATINDEX('%[^0-9-]%',DatabaseField),42,''),'-','.'),1)
from @test;
 

Тест на db<>fiddle здесь

Как и в (теперь удаленном) ответе, я бы использовал PARSENAME его для разделения на 2 части. Затем вы можете использовать PATINDEX , чтобы найти первый нечисловой символ и получить LEFT символы из этого:

 SELECT LEFT(PN.Field1,ISNULL(NULLIF(PATINDEX('%[^0-9]%',PN.Field1),0),LEN(Field1))) AS Field1,
       LEFT(PN.Field2,ISNULL(NULLIF(PATINDEX('%[^0-9]%',PN.Field2),0)-1,LEN(Field2))) AS Field2
FROM (VALUES('1185731-2148838B'),
            ('1185731-2148838S'),
            ('1185731-2148838W'),
            ('1185731-2148839B'))V(YourString)
      CROSS APPLY (VALUES(PARSENAME(REPLACE(V.YourString,'-','.'),2),PARSENAME(REPLACE(V.YourString,'-','.'),1)))PN(Field1,Field2);
 

Вот как я бы это сделал:

 DECLARE @test TABLE (DatabaseField VARCHAR(30));
INSERT @test VALUES('1185731-2148838B'), ('1234567-1234567?'),
                   ('1185731-214883612WAH'), ('1185731-2148839BLAH');

SELECT
  t.DatabaseField,
  NewA = SUBSTRING(clean.S,1,f1.Pos),
  NewB = SUBSTRING(clean.S, f1.Pos 2, 30)
FROM        @test AS t
CROSS APPLY (VALUES(CHARINDEX('-', t.DatabaseField)-1))        AS f1(Pos)
CROSS APPLY (VALUES(PATINDEX('%[^0-9-]%', t.DatabaseField)-1)) AS f2(Pos)
CROSS APPLY (VALUES(SUBSTRING(t.DatabaseField,1,f2.Pos)))      AS clean(S);
 

ВОЗВРАТ:

 DatabaseField                  NewA                           NewB
------------------------------ ------------------------------ ------------------------------
1185731-2148838B               1185731                        2148838
1234567-1234567?               1234567                        1234567
1185731-214883612WAH           1185731                        214883612
1185731-2148839BLAH            1185731                        2148839
 

Вы можете определить функцию для проверки столбца на соответствие РЕГУЛЯРНОМУ выражению и удаления любых нечисловых символов в строке. и используйте это в своем запросе.

Обновленный ответ, чтобы учесть большую длину и избежать ошибок с индексом символов для столбца 2. Разделение основано на первом найденном ‘-‘. Добавлено также несколько примеров

 CREATE OR ALTER FUNCTION dbo.fnGetNumbersOnly
(
    @StringToCheck NVARCHAR(MAX)
)
RETURNS @Results TABLE
(
    NumbericValue BIGINT
)
/*****************************************************************************************************************************************************
SELECT dbo.fnGetNumbers('21488-d*39B') AS Result
*****************************************************************************************************************************************************/
AS
BEGIN
    DECLARE @Start INT = 1,
            @End   INT = ISNULL (LEN (@StringToCheck), 0)
    DECLARE @Result NVARCHAR(MAX) = N''

    WHILE @Start <= @End
    BEGIN
        DECLARE @Char CHAR(1) = SUBSTRING (@StringToCheck, @Start, 1)

        IF (@Char NOT LIKE '[0-9]')
        BEGIN
            SET @Start  = 1;

            CONTINUE
        END

        SET @Result  = @Char
        SET @Start  = 1;
    END

    INSERT INTO @Results
    (
        NumbericValue
    )
    SELECT @Result

    RETURN
END
GO

SELECT      c.stringtosplit AS GivenData,
            a.NumbericValue AS NewColumnA,
            b.NumbericValue AS NewColumnB
FROM
            (
                SELECT '1185731-2148838B' AS stringtosplit
                UNION ALL
                SELECT '1185731-2148838S'
                UNION ALL
                SELECT '1185731-2148838W'
                UNION ALL
                SELECT '1185731-2148839B'
                UNION ALL
                SELECT '1185-731-21484-#839B'
                UNION ALL
                SELECT '1185731-2148yt839B'
            ) AS c
CROSS APPLY dbo.fnGetNumbersOnly (LEFT(c.stringtosplit, CHARINDEX ('-', c.stringtosplit))) AS a
CROSS APPLY dbo.fnGetNumbersOnly (REPLACE (c.stringtosplit, LEFT(c.stringtosplit, CHARINDEX ('-', c.stringtosplit)), '')) AS b
GO
 

Еще одно предложение:

 SELECT t.YourField
      ,B.firstPart
      ,CASE WHEN posNonNum < 1 THEN B.secondPart ELSE LEFT(B.secondPart,posNonNum) END AS secondPart
FROM YourTable t
CROSS APPLY(VALUES(CHARINDEX('-',t.YourField))) A(posHyphen)
CROSS APPLY(VALUES(LEFT(t.YourField,posHyphen-1),RIGHT(t.YourField,LEN(t.YourField)-posHyphen))) B(firstPart,secondPart)
CROSS APPLY(VALUES(PATINDEX('%[^0-9]%',B.secondPart))) C(posNonNum);
 

Идея вкратце:

• Первый APPLY добавляет позицию разделяющего дефиса в результирующий набор
• Второй APPLY используется LEFT() для получения первой части и RIGHT() возврата второй части.
• Третий APPLY будет искать первый нечисловой символ во второй части.
• В финале SELECT добавляется a CASE для решения ситуаций, в которых PATINDEX() не найдено ни одного нечислового символа.