Split field values using specific character by mysql

Question

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!

I'm guessing the second value should really be "2K".

Gordon Linoff
– Gordon Linoff

2015-07-09 01:15:23 +00:00
Commented Jul 9, 2015 at 1:15 — Gordon Linoff
– Gordon Linoff, Commented Jul 9, 2015 at 1:15

Sachu · Accepted Answer · 2015-07-09 01:05:12Z

1

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

answered Jul 9, 2015 at 1:05

Sachu

7,7549 gold badges59 silver badges101 bronze badges

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

7 Comments

Myrts Grc Over a year ago

thanks for the answer it worked! :) But one thing I want to know, what if my data have a special character like "?"

Sachu Over a year ago

@MyrtsGC "?" come after digit or in between digit?

Sachu Over a year ago

o/p will be num=0 and letter = ?

Myrts Grc Over a year ago

and also what if my data have a "/" special character, between the digit? Thanks a lot!

Sachu Over a year ago

@MyrtsGrc if any charachter come in between the digit this will not work.

|

Collectives™ on Stack Overflow

Split field values using specific character by mysql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related