Extracting Number From String SQL

Question

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";}

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.

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);

But not SQL. Thanks in advance

So you can't extract that number after CO_ in an SQL statement? Ignore the PHP part, as that gives me the answer after the sql statement. I need it in the SQL statement. Thought it would be possible. — andy
– andy, Commented Nov 4, 2014 at 9:30

Mihai Iorga · Accepted Answer · 2014-11-04 09:42:51Z

2

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`

In PHP you can just unserialize():

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

eg:

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

edited Nov 4, 2014 at 9:42

answered Nov 4, 2014 at 9:25

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

andy Over a year ago

The issue lies that I need it within the SQL statement so it'd be something like TRIM(UF_CRM_TASK, C0_.....) AS companyID so that I can use it in the WHERE clause. I don't want it as PHP.

Mihai Iorga Over a year ago

MySQL doesn't know what a PHP serialization is.

Mihai Iorga Over a year ago

Solved it in MySQL :)

elzapp Over a year ago

In a WHERE clause you can use regular expressions as well, though, this is a better way

Michael Nielsen · Accepted Answer · 2014-11-04 10:28:19Z

You need to use CharIndex and SubString (Microsoft SQL) or

This is the sample code I made for my Microsoft SQL server:

declare @companyIdString varchar(50) = 'a:1:{i:0;s:7:"CO_2012";}'

print 'Company ID in a string: ' + @companyIdString
print 'Find first position: ' + Cast(charindex('"CO_', @companyIdString) as varchar(2))
print 'Locate the second position (the last "): ' + Cast(charindex('"', @companyIdString, charindex('"CO_', @companyIdString)+4) as varchar(2))
print 'Extracted Company Id: ' + substring(@companyIdString,charindex('"CO_', @companyIdString)+4, charindex('"', @companyIdString, charindex('"CO_', @companyIdString)+4) - charindex('"CO_', @companyIdString) - 4)

select 
@companyIdString as CompanyIdString,
substring(@companyIdString,charindex('"CO_', @companyIdString)+4, charindex('"', @companyIdString, charindex('"CO_', @companyIdString)+4) - charindex('"CO_', @companyIdString) - 4) as CompanyId

I also made the same code on a mySQL server:

set @companyIdString := 'a:1:{i:0;s:7:"CO_2012";}';

select 
@companyIdString as CompanyIdString, 
substring_index(substring_index(substring_index(@companyIdString, '"', 2), '"', -1), '_', -1) as CompanyId

The substring_index starts by locating the second " (string is now a:1:{i:0;s:7:"CO_2012), then it searches backward with the -1 to locate the first " (string is now CO_2012). And then it searches backward for the underscore (string is now 2012).

Collectives™ on Stack Overflow

Extracting Number From String SQL

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related