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

  • Valiant
    Valiant Coach
    Answer ✓

    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
    Sridhar Member
    Answer ✓

    Thank you Valiant!! That helps.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    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
    Sridhar Member
    Answer ✓

    Thank you Valiant!! That helps.