CODEWITHSUNDEEP
mysqlsql
Myrts Grc
Myrts Grc

Reputation: 53

Split field values using specific character by mysql

sorry for the repeated question. I already asked for help about this by the use of ORACLE database. But now I really wanted to know how can I split this using MySQL database

It is possible to split field values using a specific character? Here is my sample table

value
10uF
2K
1.0uF
200UF

I want it to split by this:

 value       capacitance/resistance
 10            uF
 2             K
 1.0           uF
 200           UF

Hope you can help me once again. Thanks! and more power!

Upvotes: 0

Views: 86

Answers (1)

Sachu
Sachu

Reputation: 7766

You can use below code

create table temp
(value varchar(10)
 );

insert into temp values ('10uF');
insert into temp values('2K');

SELECT value + 0 AS num
     , REPLACE(value,value+0,'') AS unit
  FROM temp

O/P

num letter
10  uF
2   K

The trick the query is using is to evaluate the column value in a numeric context, by adding a zero. MySQL will return the numeric value.

But this wont work in case of 10Uf10,2k3..

Hope all your data is digit + charachter

Fiddle for the same

Upvotes: 1

Related Questions