Извлечь число между двумя подстроками в sql

Question

У меня был предыдущий вопрос, и это заставило меня начать, но теперь мне нужна помощь, чтобы закончить это. Предыдущий вопрос = Как искать строку и возвращать только числовое значение?

По сути, у меня есть таблица с одним из столбцов, содержащая очень длинную строку XML. Есть номер, который я хочу извлечь в конце. Образец номера будет такой...

<SendDocument DocumentID="1234567">true</SendDocument>

Поэтому я хочу использовать подстроки, чтобы найти первую часть = true, чтобы я оставался только с номером.

То, что я пробовал до сих пор, это:

SELECT SUBSTRING(xml_column, CHARINDEX('>true</SendDocument>', xml_column) - CHARINDEX('<SendDocument',xml_column) +10087,9) 

Выше приведены результаты, но это далеко не так. Меня беспокоит то, что, если число увеличится с 7 до 8 или 9 или 10 цифр?

В предыдущем вопросе мне помогли с этим:

SELECT SUBSTRING(cip_msg, CHARINDEX('<SendDocument',cip_msg)+26,7)

и вот как я начал, но я хотел изменить, чтобы я мог вычесть последнюю часть и просто остаться с цифрами.

Итак, еще раз, первая часть строки, которая содержит цифры, найдите две подстроки вокруг цифр и удалите их и получите только цифры независимо от длины.

Спасибо вам всем

Answer 1

Вы должны иметь возможность настроить SUBSTRING() так, чтобы начальная и конечная позиции были переменными. Таким образом, длина самого числа не имеет значения.

Судя по звуку, начальная позиция, которую вы хотите, это прямо после "истины"

Начальная позиция будет:

CHARINDEX('<SendDocument DocumentID=', xml_column) + 25
((adding 25 because I think CHARINDEX gives you the position at the beginning of the string you are searching for))

Длина будет:

CHARINDEX('>true</SendDocument>',xml_column) - CHARINDEX('<SendDocument DocumentID=', xml_column)+25
((Position of the ending text minus the position of the start text))

Итак, как насчет чего-то вроде:

SELECT SUBSTRING(xml_column, CHARINDEX('<SendDocument DocumentID=', xml_column)+25,(CHARINDEX('>true</SendDocument>',xml_column) - CHARINDEX('<SendDocument DocumentID=', xml_column)+25))

Answer 2

Вы пытались работать напрямую с типом xml? Как ниже:

DECLARE @TempXmlTable TABLE
(XmlElement xml )

INSERT INTO @TempXmlTable
select Convert(xml,'<SendDocument DocumentID="1234567">true</SendDocument>')



SELECT
element.value('./@DocumentID', 'varchar(50)') as DocumentID
FROM
@TempXmlTable CROSS APPLY
XmlElement.nodes('//.') AS DocumentID(element)
WHERE   element.value('./@DocumentID', 'varchar(50)')  is not null

Если вы просто хотите работать с этим как со строкой, вы можете сделать следующее:

DECLARE @SearchString varchar(max) = '<SendDocument DocumentID="1234567">true</SendDocument>'
DECLARE @Start int = (select CHARINDEX('DocumentID="',@SearchString)) + 12 -- 12 Character search pattern
DECLARE @End int = (select CHARINDEX('">', @SearchString)) - @Start --Find End Characters and subtract start position

SELECT SUBSTRING(@SearchString,@Start,@End)

Ниже приведена расширенная версия анализа строки документа XML. В приведенном ниже примере я создаю копию функции PLSQL с именем INSTR, по умолчанию в базе данных MS SQL ее нет. Функция позволит мне искать строки в указанной начальной позиции. Кроме того, я разбираю пример строки XML в таблицу переменных temp в строки и смотрю только те строки, которые соответствуют моим критериям поиска. Это потому, что может быть много элементов со словами DocumentID, и я хочу найти их все. Увидеть ниже:

IF EXISTS (select * from sys.objects where name = 'INSTR' and type = 'FN')
DROP FUNCTION [dbo].[INSTR]
GO

CREATE FUNCTION [dbo].[INSTR] (@String VARCHAR(8000), @SearchStr VARCHAR(255), @Start INT, @Occurrence INT)
RETURNS INT
AS
BEGIN
DECLARE @Found INT = @Occurrence,
@Position INT = @Start;

WHILE 1=1
BEGIN
-- Find the next occurrence
SET @Position = CHARINDEX(@SearchStr, @String, @Position);

-- Nothing found
IF @Position IS NULL OR @Position = 0
RETURN @Position;

-- The required occurrence found
IF @Found = 1
BREAK;

-- Prepare to find another one occurrence
SET @Found = @Found - 1;
SET @Position = @Position + 1;
END

RETURN @Position;
END
GO

--Assuming well formated xml
DECLARE @XmlStringDocument varchar(max) =   '<SomeTag Attrib1="5">
                                            <SendDocument DocumentID="1234567">true</SendDocument>
                                            <SendDocument DocumentID="1234568">true</SendDocument>
                                            </SomeTag>'

--Split Lines on this element tag
DECLARE @SplitOn nvarchar(25) = '</SendDocument>' 

--Let's hold all lines in Temp variable table
DECLARE @XmlStringLines TABLE
    (
        Value nvarchar(100)
    ) 

        While (Charindex(@SplitOn,@XmlStringDocument)>0)
        Begin

            Insert Into @XmlStringLines (value)
            Select 
                Value = ltrim(rtrim(Substring(@XmlStringDocument,1,Charindex(@SplitOn,@XmlStringDocument)-1)))

            Set @XmlStringDocument = Substring(@XmlStringDocument,Charindex(@SplitOn,@XmlStringDocument)+len(@SplitOn),len(@XmlStringDocument))
        End

        Insert Into @XmlStringLines (Value)
        Select Value = ltrim(rtrim(@XmlStringDocument))

    --Now we have a table with multple lines find all Document IDs
    SELECT 
    StartPosition = CHARINDEX('DocumentID="',Value) + 12,
    --Now lets use the INSTR function to find the first instance of '">' after our search string
    EndPosition = dbo.INSTR(Value,'">',( CHARINDEX('DocumentID="',Value)) + 12,1),
    --Now that we know the start and end lets use substring
    Value = SUBSTRING(value,( 
                -- Start Position
                CHARINDEX('DocumentID="',Value)) + 12, 
                    --End Position Minus Start Position
                dbo.INSTR(Value,'">',( CHARINDEX('DocumentID="',Value)) + 12,1) - (CHARINDEX('DocumentID="',Value) + 12))
    FROM 
        @XmlStringLines 
    WHERE Value like '%DocumentID%' --Only care about lines with a document id