I have an issue while am trying to pull and create specific column output in SQL

select 
left(PostalCode,2) as area
,case substring(PostalCode,6,1) 
 when substring(PostalCode,6,1) = 0 then '100'
 when substring(PostalCode,6,1) > 0 then (substring(PostalCode,6,1))*100
 else 'NotDigit'
end   substring(PostalCode,6,1) as [location]
from province_table 

I tried many ways to restructure the query above and am always unable to get a result

expected result

looking forwards to your kind help

Thanks

CodePudding user response：

select 
left(PostalCode,2) as area
,case when isnumeric(substring(PostalCode,6,1)) = 1 and substring(PostalCode,6,1) = 0 then cast('100' as varchar(max))
      when isnumeric(substring(PostalCode,6,1)) = 1 and substring(PostalCode,6,1) > 0 then cast(substring(PostalCode,6,1)*100 as varchar(max))
      else 'NotDigit' end as result
from province_table 

Fiddle