-
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
-
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|