Extract only number from string

Transact-SQL
1

I have to following sql command to extract only numbers from a string :

update Oesskattings 
    SET alfasorteer1 =   CASE WHEN CHARINDEX('-', blokno) > 0 THEN SUBSTRING(blokno +'-',0, CHARINDEX('-', blokno))
    ELSE SUBSTRING(blokno, PATINDEX('%[0-9]%', blokno), LEN(blokno)) 
end

My problem is when I have a record where blokno is eg 1B (conversion failed where number is followed by character).

How can I improve my code?

Regards

2

Maybe you can make this case statement work for you:

with cte(blokno)
  as (          select '1'
      union all select 'a2'
      union all select '3b'
      union all select 'c4d'
     )
select blokno
      ,case
          when patindex('%[0-9]%',blokno)>0
          then reverse(substring(reverse(substring(blokno+'ยค'
                                                  ,patindex('%[0-9]%',blokno)
                                                  ,len(blokno)+1
                                                  )
                                        )
                                ,patindex('%[0-9]%'
                                         ,reverse(substring(blokno+'-'
                                                           ,patindex('%[0-9]%',blokno)
                                                           ,len(blokno)+1
                                                           )
                                                 )
                                         )
                                ,len(blokno)+1
                                )
                      )
          else ''
       end
  from cte
;
3

Thank you bitsmed. I really appreciate. What worked for me was to delete all non-numeric characters from the string with :

  set alfasorteer1 = substring(blokno, patindex('%[0-9]%', blokno), 1+patindex('%[0-9][^0-9]%', blokno+'x')-patindex('%[0-9]%', blokno))

Thank you once again.