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

   MEDevel.com: Open-source for Healthcare and Education

   Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

   Visit Medevel

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’

Tried

 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.

DEMO

>Solution :

Modify your query as the following:

SELECT 
  name, 
  CAST(SUBSTRING_INDEX(currencyType, 'X', 1) AS UNSIGNED) AS count,
  CASE 
    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.