In Oracle, SELECT query (pulling data), how do you change the description for a particular entry?

Transact-SQL
1

I have a field in my table that provides description for vehicles (Cars, Trucks, Vans, etc.). Unfortunately the descriptions are not all uniform....

Example:
CAR#61
CAR # 124
CAR# 469
TRUCK#123

I need the data to read as follows:

CAR#XXX - No spaces, if the car is under the digits zero fill to make 3 digits. Example: CAR#3 should read as CAR#003 .

I just need this for the Cars. Trucks, Vans, etc. shouldn't be changed at all.
Also, I just have 'read-only' access on the DB so I cant do updates to the table. I need to pull the data through a SELECT query (Oracle Database).

2

this is a microsoft sql forum but here is a stab not sure if the functions used exist in ORACLE

declare @pakdaka table(ka_name varchar(50))

insert into @pakdaka
select 'CAR#61' union
select 'CAR # 124' union
select 'CAR# 469' union
select 'TRUCK#123' 

select ka_name,
string + case 
   when len(numeros) = 1 then '00' + numeros
   when len(numeros) = 2 then '0' + numeros
   else numeros
   end as duble
  from (
        select ka_name,
		substring(replace(ka_name, ' ',''),charindex('#',replace(ka_name, ' ','')) +1, len(replace(ka_name, ' ',''))) numeros,
		substring(replace(ka_name, ' ',''),0, charindex('#',replace(ka_name, ' ','')) +1) string		
		From @pakdaka
      ) a
1 Like