# Miscellaneous > SQL Scripts >  Replace Function or !!!!!!!!

## sleezy

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.

----------


## JBane

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

----------


## sleezy

Thanks Mate !!!!!!!

----------


## lvp

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

----------


## MAK

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

totalspace is the column name.

----------


## lvp

Thank you very much.

----------

