How can I extract values from one column and create 2 separate columns?
Hi I have a column in my dataset with values that looks like "711-500001-3068-22-STL-000000-00000000000". I wanted to take that value and create two columns. First column will have the value of "3068" and second column with "22". What's the best wat to accomplish this?
Best Answers
-
This is going to depend on the how that column is being populated to begin with. Does your column always get populated with 3 digits - 6 digits - 4 digits etc? Or are you always looking for the number after the 2nd dash?
If it's based on digits you could use a SUBSTRING function:
https://www.w3schools.com/sql/func_mysql_substring.asp
If it's based on the # of -'s then you can could use a SUBSTRING_INDEX function:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index
https://stackoverflow.com/questions/14347581/mysql-second-or-third-index-of-in-string
With substring you tell it where to start (your index) and how long of a string you want. Substring_Index will return the left or right portion of the string based on the characters you give it and the instance number you're looking for. You can combine 2 Substring Index functions to give you the mid portion of a string.
Hope that helps. And if you need further assistance, let me know more about the parameters for what you're wanting to include.
Sincerely,
Valiant**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Thank you Valiant!! That helps.
0
Categories
- 7.3K All Categories
- 13 Getting Started in the Community
- 142 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 54 Domo Everywhere
- 2K Charting
- 1K Ideas Exchange
- 904 Connectors
- 237 Workbench
- 342 APIs
- 77 Apps
- 19 Governance & Productivity
- 235 Use Cases & Best Practices
- 50 News
- 473 Onboarding
- 573 日本支部