dcsimg
Results 1 to 6 of 6

Thread: Replace Function or !!!!!!!!

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    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.

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    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

  3. #3
    Join Date
    Sep 2002
    Posts
    218

    Thank You

    Thanks Mate !!!!!!!

  4. #4
    Join Date
    Jun 2005
    Posts
    2
    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

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    select right('00000'+convert(varchar(10),totalspace),5) as totalspace from
    diskspace where servername ='atutil1' and drive='c'

    totalspace is the column name.

  6. #6
    Join Date
    Jun 2005
    Posts
    2
    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •