How to remove special characters from cells and parse data in sql
I have data stored as text and am wanting to convert it to int within SQL. Currently the text reads (8'6 11/16"). I am needing to remove the foot symbol ('), the dash (), and the inches (") symbol. Then take each number (stored as text) and convert to int. At which point I would rebuild the cell to read 102 11/16". If it is possible to convert each number (stored as text) to decimal, then I could do a easy conversion and rebuild into fraction format. The SUBSTRING() command is not recognized in the "Transform" of MySQL DataFLow.
Best Answer

Thanks for your help,
I was able to use the SUBSTRING_INDEX() to get the specific substring I was looking for. One issue I had was that my data does not always have a fraction (/) character. So, I had to run a SUBSTRING_INDEX() to get everything between the () and (").
I then needed to get the fraction seperated. So I used the same SUBSTRING_INDEX() method, but added the the commands: WHERE and REGEXP '/'. With the WHERE and REGEXP command added I was able to pull only the fraction and not the whole number.
In line 18, 19, and 20 of my output table I was able to get only the fraction and skipped the rows that did not contain a fraction.
Thanks for your help with the SUBSTRING_INDEX().
1
Answers

What kinds of problems are you having with SUBSTRING()? I've used that plenty of times in dataflows, so I'm surprised to see you're having an issue.
What kind of variety do you see in your string data? Do each of these parameters (feet, inches, fraction numerator and denominator) exist every time (like 8'0 0/16" vs 8' for eight feet)? Or are the excluded if they won't exist as part of that measurement?
If they are consistent, a series of substring functions should work great. SUBSTRING_INDEX() could work really well here, too.
Once you get SUBSTRING() or SUBSTRING_INDEX() working, using a dataflow to convert or cast to decimal should be simple.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 
The SUBSTRING() does not work in my situation because I am trying to use it on a column with varying information. In my column I will have information in just inches (8 1/2") as well as information in ft and in (5'6 1/8"). I have been successful in using the LEFT() command to remove all the (") special characters. I am unable to get the RIGHT() command to work properly. I want to use RIGHT() to keep everything to the right of the () in 5'6 1/8. The LEFT() command removed the (") but when I try the RIGHT() command the result I get is (1/8). I am missing the 6.
LEFT() CODE:
SELECT LEFT(height, LOCATE('"',height)1) AS length_in, name
FROM 96280_shipcon_tableRIGHT() CODE:
SELECT RIGHT(length_in, LOCATE('',length_in)+1) AS length_in_fin, name
FROM length_inThanks for your help.
0 
Variability makes it more complex for sure. Will there always be a fraction? That could matter in your approach as well.
I tinkered a little bit. There has to be a better way but see if some of this helps (also see attached):
select
'8''4 3/16"' as length
, case when '8''4 3/16"' like '%''%'  if there are feet measurements
then cast(substring_index('8''4 3/16"','''',1) as unsigned)  take the digits until the feet character
else 0  otherwise no feet
end feet
, case when '8''4 3/16"' not like '%''%'  if there are no feet measurements
then cast(substring_index('8''4 3/16"',' ',1) as unsigned)  take everything up to the space
else TRIM(SUBSTRING('8''4 3/16"', LOCATE('','8''4 3/16"')+1,2))  if there are feet measurements, take everything between the  and the space
end inches
, SUBSTRING('8''4 3/16"', LOCATE(' ','8''4 3/16"')+1, length('8''4 3/16"')LOCATE(' ','8''4 3/16"')1) inches_fractionAaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 
Thanks for your help,
I was able to use the SUBSTRING_INDEX() to get the specific substring I was looking for. One issue I had was that my data does not always have a fraction (/) character. So, I had to run a SUBSTRING_INDEX() to get everything between the () and (").
I then needed to get the fraction seperated. So I used the same SUBSTRING_INDEX() method, but added the the commands: WHERE and REGEXP '/'. With the WHERE and REGEXP command added I was able to pull only the fraction and not the whole number.
In line 18, 19, and 20 of my output table I was able to get only the fraction and skipped the rows that did not contain a fraction.
Thanks for your help with the SUBSTRING_INDEX().
1
Categories
 10.5K All Categories
 8 Connect
 918 Connectors
 250 Workbench
 470 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 477 Datasets
 193 Visualize
 252 Beast Mode
 2.1K Charting
 11 Variables
 17 Automate
 354 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 247 Distribute
 63 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 174 Product Ideas
 1.2K Ideas Exchange
 12 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive