Remove Alphabet and Insert Leading 0 up to Certain Character Length

Question

I have a table name Table and column name Item. Below is the data.

ABC123
ABC1234
ABC12345
HA11
K112
L1164

I need to remove the alphabets, replace them with leading 0, and the total character length must be 9. Below is the results.

000000123
000001234
000012345
000000011
000000112
000001164

I know how to change for ABC (certain alphabet set) only however I don't know to build the CASE statement. Below is what I have been successful.

select REPLICATE('0',9-LEN(A.B)) + A.B 
from 
(select replace(Item, 'ABC','') as B from Table) as A

I tried to combine CASE with SELECT and it doesn't seem like it.

Case when Item like '%ABC%' then 
          select REPLICATE('0',9-LEN(A.B)) + A.B 
          from 
          (select replace(Item, 'ABC','') as B from Table) as A
     when Item like '%HA%' then 
          select REPLICATE('0',9-LEN(A.B)) + A.B 
          from 
          (select replace(Item, 'HA','') as B from Table) as A
     when Item like '%K%' then 
          select REPLICATE('0',9-LEN(A.B)) + A.B 
          from 
          (select replace(Item, 'K','') as B from Table) as A
     when Item like '%L%' then 
          select REPLICATE('0',9-LEN(A.B)) + A.B 
          from 
          (select replace(Item, 'L','') as B from Table) as A
     else Item
     End

Does anyone know how to achieve the result? I'm using SQL Server 2012.

Thank you.

Answer 1

I assumed, that you have letters only at the beginning of your data.

declare @s varchar(20) = 'ABS123'
-- we find index of first occurence of digit and then we cut out the letters
set @s = right(@s, len(@s) - patindex('%[0-9]%', @s) + 1)
-- here we just produce string with amount of zeros we need
select left('000000000', 9 - len(@s)) + @s

In terms of applying it to your table:

select left('000000000', 9 - len([Digits])) + [Digits] from (
    select right([Item], len([Item]) - patindex('%[0-9]%', [Item]) + 1) as [Digits] from [Table]
)