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.
split currentyType column by
return count (count must be an int value – not string)
for Rupee – count is 5,
for Dollar – count is 3
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.
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
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
INSTR function check if the string contains ‘$’ or not.