从字符串 SQL 中提取数字 [英] Extracting Number From String SQL

问题描述

我有一个普通的 SQL 语句:

I have a normal SQL statement:

SELECT VALUE_ID, UF_CRM_TASK FROM b_uts_tasks_task

现在每次都会返回一个不同的字段，但它们采用以下形式:

Now this returns a a different field everytime but they take the form of the following:

a:1:{i:0;s:7:"CO_2012";} or a:1:{i:0;s:5:"CO_12";} or a:1:{i:0;s:7:"CO_2017";}

基本上它们每次都不同.我需要的是在 CO_ 部分之后获取数字.我试过 TRIM 但因为前导和尾随部分的所有内容都发生了变化，我认为这行不通.

Basically they're different everytime. What I need is to just get the number after the CO_ part. I have tried TRIM but because everything changes in the leading and trailing section I don't think this would work.

我在 Stack Overflow 上看了一段时间，但找不到它.我知道如何在 PHP 中做到这一点:

I have looked on Stack Overflow for a while and cannot find it. I know how to do it in PHP:

$data = $row['UF_CRM_TASK'];    
$companyID = substr($data, strpos($data, "CO_") + 1);
$newCompanyID = preg_replace('/[^0-9.]+/', '', $companyID);

但不是 SQL.提前致谢

But not SQL. Thanks in advance

推荐答案

在 MYSQL 中有点丑:

In MYSQL is a bit ugly:

/*SUBSTRING_INDEX BASED ON CO_ AND THE LAST " - in 2 SUBSTRINGS*/
SELECT `VALUE_ID`, SUBSTRING_INDEX(SUBSTRING_INDEX(`UF_CRM_TASK`, 'CO_', -1), '"', 1) AS `COMPANY_ID` FROM `b_uts_tasks_task`

在 PHP 中你可以unserialize():

In PHP you can just unserialize():

$data = unserialize($row['UF_CRM_TASK']);
$companyID = str_replace('CO_', '', $data[0]);

例如:

$data = unserialize('a:1:{i:0;s:5:"CO_12";}');
echo str_replace('CO_', '', $data[0]);
//==> 12

这篇关于从字符串 SQL 中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！