How can I extract values from one column and create 2 separate columns?

Sridhar

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?

BlueRooster

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.

Sridhar

Thank you Valiant!! That helps.

BlueRooster

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.

Sridhar

Thank you Valiant!! That helps.