sql—从具有多个数字的消息中标识特定数字

r8xiu3jd  于 2021-07-26  发布在  Java
关注(0)|答案(5)|浏览(260)

我需要从消息中选取第三个数值。这个数字可以是1长度,2长度,4长度等。我用patindex得到数字的索引,但不知道如何让它直接寻找第三个数字。
信息:

'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'

查询:

select (PATINDEX('%[0-9]%', 'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table')

修改的查询:

SELECT SUBSTRING
('Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table .%' ,
PATINDEX('%total of%' ,'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table .%') + 9, 
5)

我得到的输出是6609,它只特定于此消息,但是如果我的消息是“成功导入6条记录并更新了0条记录,总共有6条记录到表中”,那么我就没有得到预期的输出。
预期产量:

6609 -- 3rd numeral
velaa5lx

velaa5lx1#

如果您的第三个数字是字符串中的最后一个数字(总是),您可以使用下面的逻辑来获取第三个数字-
此处演示

DECLARE @T VARCHAR(1000)
SET @T = 'Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'

SELECT 
REVERSE
(
    SUBSTRING
    (
        REVERSE(@T),
        PATINDEX('%[0-9]%', REVERSE(@T)),
        (CHARINDEX(' ',REVERSE(@T),PATINDEX('%[0-9]%', REVERSE(@T))))-(PATINDEX('%[0-9]%', REVERSE(@T)))
    )
)
elcex8rz

elcex8rz2#

也许不是最好的解决方案,但它是有效的:

WITH source(message) AS
  (SELECT 'Successfully imported 1 records and updated 2 records for a total of 3 records into the table')
SELECT value
FROM SOURCE CROSS apply
  (SELECT row_number() OVER (ORDER BY message) POSITION, value
   FROM string_split(message, ' ')
   WHERE try_cast(value AS int) IS NOT NULL) AS a
WHERE POSITION = 3
stszievb

stszievb3#

一般来说,数据库不适合进行字符串操作,这种工作应该在前端进行。但是,您可以使用以下方法

SELECT TOP 1 WITH TIES T.String, SS.Value
FROM
(
  VALUES
  ('Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'),
  ('Successfully imported 6609 records and updated 0 records for a total of 9999 records into the table')
) T(String)
CROSS APPLY
(
  SELECT ROW_NUMBER() OVER(ORDER BY TRY_CAST(Value AS INT)) RN,
         Value
  FROM STRING_SPLIT(String, ' ')
) SS
ORDER BY RN DESC;
``` `Online Demo` 
ca1c2owp

ca1c2owp4#

我已经尝试了下面的查询,它的工作如预期。

SELECT SUBSTRING
('Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%' ,
PATINDEX('%total of%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') + 9, 
PATINDEX('%records into the table%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') -
(PATINDEX('%total of%' ,'Successfully imported 0 records and updated 0 records for a total of 0 records into the table .%') + 9) )
dfuffjeb

dfuffjeb5#

基本上在你的两个例子,你想要的价值后 total of ,找到它,然后找到后面的第一个空格:

SELECT TRY_CONVERT(int,SUBSTRING(V.YourString,PI.I+DATALENGTH('total of '),CHARINDEX(' ',V.YourString,PI.I+DATALENGTH('total of ')) - (PI.I+DATALENGTH('total of '))))
FROM (VALUES('Successfully imported 6609 records and updated 0 records for a total of 6609 records into the table'),
            ('Successfully imported 6 records and updated 0 records for a total of 6 records into the table .'))V(YourString)
     CROSS APPLY (VALUES(PATINDEX('%total of%',V.YourString)))PI(I)

相关问题