How do I remove any text from string SQL Server?

Question

I have a column:

| Duration  |
| --------  |
| 32 minutes|
| 27minutes |
| 20 mins   |
| 15        |

I want to remove the text so that only the numbers remain, but as the text is varied I'm at a loss how to do so. I've reviewed multiple solutions and none seem to accomplish the job in an elegant way.

I had another column that was distance, and every row contained 'km' at the end so I was able to use replace.

UPDATE runner_orders
SET distance = REPLACE(distance,'km','')

I tried doing the same but using a wildcard, this didn't work.

UPDATE runner_orders
SET duration = REPLACE(duration, 'min%','')

Any input is well appreciated.

You can use the function translate and replace as mentioned here https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — Luis Fernando Prudencio Cruz, Nov 08 '22 at 18:32

score -1 · Answer 1 · answered Nov 08 '22 at 18:10

-1

You can achieve this with CAST

UPDATE runner_orders SET duration = CAST(duration as INT);

answered Nov 08 '22 at 18:10

mustafa

36
5

How do I remove any text from string SQL Server?

1 Answers1