MySQL get value from string with char_length()

Question

I have a column that consists of details of an orderline named 'ConcatValue'. An example of a value in this column is:

573856014/100/M00558640/OrderQty12

I want to extract the order value which can be founded after 'OrderQty'. I thought I had a solution by executing the following statement: substr(ConcatValue,char_length(ConcatValue)-1,char_length(ConcatValue))

This results in only level the last 2 characters of the string from the column ConcatValue. For the ConcatValue mentioned above I will get the following result: '12'. Which is the desired result. But when the orderline has an Order quantity below 10, for example in the following ConcatValue:573856014/100/M00558640/OrderQty3

I will get the following result: y3

My question: Is there a way to delete 'y' if a row has an y within the value? Or is there a way to replace the y with a 0? Or is there a way to only select the last digits from the ConcatValue string?

forpas · Accepted Answer · 2020-11-30 09:00:13Z

1

Use string functions.
With substring_index() you can get the last part of the string and with replace() remove 'OrderQty':

select replace(
         substring_index(ConcatValue, '/', -1),
         'OrderQty',
         ''
       )
from tablename

answered Nov 30, 2020 at 9:00

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-11-30 12:48:08Z

0

Actually, the simplest method is simply:

select substring_index(ConcatValue, 'OrderQty', -1)

answered Nov 30, 2020 at 12:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL get value from string with char_length()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related