0

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.

Adhitya Sanusi
  • 119
  • 2
  • 17

1 Answers1

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]
)
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • Thanks Michal. It works =D. So to change the whole column, do I need to declare the value one by one? Because I tried to change the value into declare @s varchar (20) = select item from Table, it doesn't like it. Thanks again for your help =D. – Adhitya Sanusi Sep 22 '17 at 06:38
  • Thanks Michal. You did it again =D. Sorry taking so long to reply. Been in holiday for while. – Adhitya Sanusi Oct 19 '17 at 02:35