How can I zero fill these fields?

[NSMan]

Can somebody tell me how to zero fill these? It reads this...

c1
LAN2496 139
LAN2496 14
LAN2496 141
LAN2496 142
LAN2496 143
LAN2496 144
LAN2496 145
LAN2496 146
LAN2496 147
LAN254 12

And it wann read this...

c1
LAN002496 0139
LAN002496 0014
LAN002496 0141
LAN002496 0142
LAN002496 0143
LAN002496 0144
LAN002496 0145
LAN002496 0146
LAN002496 0147
LAN000254 0012

I know I've gotta utilize the len(c1) and charindex(' ',c1), but the noisy office has shot my very limited supply of concentration powers....

 

[dbomrrsm]

select left('LAN2496 139',3)+'00'+substring('LAN2496 139',4,4)+
' '+right('00'+substring('LAN2496 139',patindex('% %','LAN2496 139')+1,50),4)

obviously just substitute your field name where 'LAN2496 139' exists.

to update do

Update YourTable
Set c1 = select left(ci,3)+'00'+substring(ci,4,4)+
' '+right('00'+substring(ci,patindex('% %',ci)+1,50),4)

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

sorry not quite right but I think all you need to do is a couple of update and accounting for the carious lengths both from start to finissh and also from start to

patindex('% %',c1)

[blue]DBomrrsm[/blue]