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'

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

CodePudding user response：

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.