Question

我正在运行以下查询 :

SELECT
    SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21) AS CVSS_Base_Score_Text,
    CONVERT(SUBSTRING_INDEX(SUBSTRING(pluginText,
        LOCATE( cvss , pluginText), 21),  :  ,-1),UNSIGNED INTEGER)
    AS CVSS_Base_Score_Number
FROM vulnerabilities_internal
WHERE LCASE(pluginText) REGEXP  .*cvss.*

它又返回两列

CVSS_Base_Score_Text         CVSS_Base_Score Number
CVSS Base Score: 4.3         4

如何修改此查询, 使 CVSS_ Base_ Score_ number 列中的数字随小数点而移到小数点, 并显示为4.3?

我对自己的问题表示敬佩:

SELECT
SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21) AS CVSS_Base_Score_Text,
CONVERT(SUBSTRING_INDEX(SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21),  :  ,  
-1), 
DECIMAL(10,1)) AS CVSS_Base_Score_Number
FROM vulnerabilities_internal

Answer 1

SELECT 
SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21) AS CVSS_Base_Score_Text, 
CONVERT(SUBSTRING_INDEX(SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21),  :  ,   
-1),  
DECIMAL(10,1)) AS CVSS_Base_Score_Number 
FROM vulnerabilities_internal

Answer 2

下面是给出理想结果的查询。

SELECT
    SUBSTRING(pluginText,LOCATE( cvss , pluginText), 21) AS CVSS_Base_Score_Text,
    CAST(SUBSTRING_INDEX(SUBSTRING(pluginText,
        LOCATE( cvss , pluginText), 21),  :  ,-1) as decimal(2,1))
    AS CVSS_Base_Score_Number
FROM vulnerabilities_internal
WHERE LCASE(pluginText) REGEXP  .*cvss.*

友情链接