Replace Function or !!!!!!!!

l have a column named Field which had prefixes like T01. , T02. etc. l would like to use the Replace function so l can end up with the field name minus the T01. etc

ie
T01.DFTES should be DFTES but
BALANCE should remain as BALANCE because it does not have the prefixes.

FIELD
=========
T01.DFTES
T02.IDBNGCDS
T03.TRDFRED
T04.SGR311
T05.GRP311
BALANCE PVA311+BAL320
T02.VFGTY
T04.LRD311
TOTAL
SUBSCRIBER


l'm currently using this query :-

SELECT
Substring(Field,5,10) AS Field
From EXACTUS_Fields
WHERE Substring(Field,1,4) like '%.%'

UNION ALL

SELECT
Field
FROM EXACTUS_Fields
WHERE Substring(Field,1,4) Not like '%.%'


Whats the best way of updating the table? Or rather what function can l use.

Assuming your prefixes are all 3 characters long:

update YourTable
set field = Substring(Field,5,10)
WHERE Substring(Field,1,4) like '%.%'



If your prefixes can be many lengths, you can use CHARINDEX to locate the period:

update YourTable
set field = right (field, (len(field) - charindex('.', field) + 1 ))
where field like '%.%'

Jeff

Thanks Mate !!!!!!!

Hello,
I have field 5 lenght numeric and I have to retrieve data from field and add zeros in front to the existing numbers for report. Could you please help
Thanks

select right('00000'+convert(varchar(10),totalspace),5) as totalspace from
diskspace where servername ='atutil1' and drive='c'

totalspace is the column name.

Thank you very much.