If a comma is your delimiter, then you can use the SUBSTR and INSTR functions to parse out the components. The SUBSTR function pulls out a portion of the given string. The INSTR function returns the location in a string. So we'll use the INSTR function to find the location of the first comma, and then pull out the STREET1 value from the ADDRESS column.
SELECT SUBSTR(address,1,INSTR(address,',')-1) AS street1 FROM temp_tbl;
Above, I found the first comma and then selected the portion of ADDRESS from the first character to the character right before the first comma. To get the CITY, you will need to search for the string between the second and third commas.
SELECT SUBSTR(address,INSTR(address,',',2)+1,INSTR(address,',',3-1) AS city FROM temp_tbl;
Play around with SUBSTR and INSTR to see how they will help you solve your problem.