How to split a string using mysql

Question

I have some data with name of medicines as shown below.

ENPARIN 100MG/1.OML INJ 00781-3500-69 
WARIN 7.5MG TAB 68382-0058-01 
TR/HC 37.5-25MG CAP 00527-1632-01

i need to get only the medicine name like

ENPARIN 100MG/1.OML
WARIN 7.5MG TAB
TR/HC 37.5-25MG CAP

How to achieve this using the string operation in mysql.

What version of mysql are you using? If 8+ something like dev.mysql.com/doc/refman/8.0/en/… might be possible. — user3783243
– user3783243, Commented Aug 10, 2020 at 18:26
Welcome to SO! Could you explain the logic a little bit more? I would say you can find the position of the last space and then use the LEFT method to get all characters up to that point, but your first example seems to be based on the second-to-last space character - is this correct? — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented Aug 10, 2020 at 18:27
The numeric part on the right hand side seems to have the same length and therefore could be chopped easily if that's enough for you. — Shadow
– Shadow, Commented Aug 10, 2020 at 18:30
Splitting this data into actual columns first would make selecting only the relevant data easy. I might suggest splitting it by spaces in a separate file, and then importing that into your database with columns like id, name, amount, method, upc so each line here becomes a row in your database, and each section of the line becomes a column. Then you could easily do SELECT name, amount, method FROM medicines;. — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented Aug 10, 2020 at 18:31

O. Jones · Accepted Answer · 2020-08-10 19:04:37Z

1

It looks like you have four subfields in your samples. And it looks like you want to retain three of them when you SELECT from your table.

But that might not be true: your question calls for transforming ENPARIN 100MG/1.OML INJ 00781-3500-69 to ENPARIN 100MG/1.OML (leaving off INJ).

At any rate this gets the first three subfields delimited by space.

SUBSTRING_INDEX('WARIN 7.5MG TAB 68382-0058-01', ' ', 3);

SUBSTRING_INDEX() is a useful string function There are many others. It's worth your time to learn about them.

If you want to remove the 12345-1234-12 numbers at the end of items that have them, you can try this

 SELECT REGEXP_REPLACE('WARIN 7.5MG TAB 68382-0058-01',
           ' [0-9]{5}-[0-9]{4}-[0-9]{2}$', '');

If you have all kinds of different layouts of these strings a large statement looking like this is in your future.

SELECT CASE WHEN something THEN something
            WHEN something THEN something
            WHEN something THEN something
            ELSE column END

This is not going to be even a little bit fun to maintain.

If possible you would be wise to split this stuff into four columns. I know your pharma supplier probably doesn't do this for you.

edited Aug 10, 2020 at 19:04

answered Aug 10, 2020 at 18:32

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

7 Comments

Earn Unlimited Over a year ago

Thank you, this works but i have a data like this too BESONE DP 0.05% CRM, SEALINE HCL 100 MG TABLET, ARONATE SODIUM 70 MG TAB. if this is the case, it shouldn't do anything.

user3783243 Over a year ago

@EarnUnlimited Could do a case when column regexp '[0-9]{3,}-[0-9]{3,}-[0-9]+' then SUBSTRING_INDEX...(solution above) else column end as column

Earn Unlimited Over a year ago

Thanks @user3783243

Earn Unlimited Over a year ago

Thank you for the inputs. Now how to achieve this, i have a string with 'MTRY,SRGTA N BM461 1197 ' and 'NO,JOHN BN1407003 1295815512 ' I need to get only the name and initial (if exists). like MTRY,SRGTA N and NO,JOHN

O. Jones Over a year ago

With respect, it doesn't make sense to use comments to show more examples of values you need to change, and ask how to change them. You should dive into the documentation for the string handling functions of MySQL and figure out a strategy. Then if you need help with a specific issue we're here for you. dev.mysql.com/doc/refman/8.0/en/string-functions.html

|

Collectives™ on Stack Overflow

How to split a string using 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