Split column value and return split values appropriately


I have below table (MYSQL),

CREATE TABLE x_table (name VARCHAR(100), currencyType VARCHAR(100));
INSERT INTO x_table VALUES ('Rupee', '5X34'), ('Dollar', '3X34$'), ('Yen', '7X57');

I want to split currencyType column into two.

  1. split currentyType column by X

  2. return count (count must be an int value – not string)
    for Rupee – count is 5,
    for Dollar – count is 3

  3. return type
    for Rupee – 34 doesn’t contain $, so type will be ‘Non-dollar’
    for dollar – 34$ contains $, type would be "dollar’
    for yen – 57 doesn’t contain $, type would be "Non-dollar’


 select name, 
        SUBSTRING_INDEX(currencyType, 'X', 1) as count,
        SUBSTRING_INDEX(currencyType, 'X', 2) as type   
 from x_table

but not sure how to implement it correctly.


>Solution :

Modify your query as the following:

  CAST(SUBSTRING_INDEX(currencyType, 'X', 1) AS UNSIGNED) AS count,
    WHEN INSTR(SUBSTRING_INDEX(currencyType, 'X', -1), '$') 
    THEN 'dollar' ELSE 'Non-dollar'
  END AS type   
FROM x_table

See demo

CAST(SUBSTRING_INDEX(currencyType, 'X', 1) AS UNSIGNED): this will cast the left part of the string as positive number.

To start searching the string from right (get the right part of the string), use -1 instead of 1 for the number parameter in the substring_index function

The INSTR function check if the string contains ‘$’ or not.

Leave a Reply Cancel reply