CODEWITHSUNDEEP
mysql
Rajesh Omanakuttan
Rajesh Omanakuttan

Reputation: 6918

MySQL - Extract only the starting numeric characters from a Text type value

In my MySQL database table, I have a column storing text-type values. One of the value is as below:

12
abc: 2
bcd_cdm30: 1
ijk_jkl: 2
ijk_mbo: 3
zero_m: 1
single_m: 0
hovered: 2
charged_one: '50'

Out of this value, I would like to extract only the first numerical characters, that is 12'. How can I do that in MySQL?

Upvotes: 0

Views: 41

Answers (1)

Shadow
Shadow

Reputation: 34232

The simplest way is to cast the field to a numeric data type. Mysql will take the leading characters of the value as long as they can be interpreted as a number and discard the rest in the conversion process.

To convert the number to numeric value, just add 0 to it:

select text_field + 0 from yourtable

Caveat: if you have a really long number there, then it may not fit into any of the numeric data types.

Upvotes: 1

Related Questions