CODEWITHSUNDEEP
mysql
roman
roman

Reputation: 33

Is it possible to select numeric characters only from a VARCHAR field in MySQL without the use of a custom function?

I'm importing data to a 3rd party CRM and the restrictions on the phone number field are tighter than those of the source database (MySQL). I would like to grab the first 10 numeric characters in the select. I don't have flexibility to sanitize the existing data. I can only work with what is there. All I can find in forums so far seems to be creating a custom function. Is a custom function the only way? The environment is MySQL 5.7.19.

Thanks in advance!

Upvotes: 0

Views: 348

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

In MySQL 5.7, here's how I'd do it:

SELECT LEFT(mycolumn, 10)+0 FROM ...

The builtin function LEFT() uses only the first N characters.

The +0 makes sure to cast it to a number, just in case the first N characters include some non-numeric characters. Casting to a numeric in MySQL just uses the leftmost number characters, and when it reaches a non-numeric character, ignores the rest.

You can get rid of hyphens first, then use the result of that as I showed above:

SELECT LEFT(REPLACE(mycolumn, '-', ''), 10)+0 FROM ...

Upvotes: 1

Related Questions