how to replace in a string

[yordangs]

hello to every one i need to create a store procedure who replace me a part of a string i have to return the string with the modifications, I made this store procedure but the problem its when it found more than one coincidence replace me all the coincidences and i only need to be replaced the inititial potition 31 to 37

here are my example

Create PROCEDURE Proc_Substring
@Tram VARCHAR(1000)
AS
declare @Id_folio int
SELECT SUBSTRING(@Tram, 31, 6) as Id_folio,
initialstate = @Tram,
Replace(@Tram,SUBSTRING(@Tram, 31, 6),'nnnnnn') as returnstate

GO

execute Proc_Substring 'BCI11000010510121438510 0010250000000000001100000000010250000000000000NDD0000000000000000000000000000'

and this returns me


001025 BCI11000010510121438510 0010250000000000001100000000010250000000000000NDD0000000000000000000000000000 BCI11000010510121438510 nnnnnn0000000000001100000000nnnnnn0000000000000NDD0000000000000000000000000000

i need this

BCI11000010510121438510 nnnnnn00000000000011000000000010250000000000000NDD0000000000000000000000000000

any one can help me ??? thanks a lot

 

[jbenson001]

that code worked for me...

 

[yordangs]

BCI11000010510121438510 nnnnnn0000000000001100000000nnnnnn0000000000000NOW0000000000000000000000000000

i need this

BCI11000010510121438510 nnnnnn00000000000011000000000010250000000000000NOW0000000000000000000000000000

 

[jbenson001]

Try:

SELECT     SUBSTRING(@Tram, 31, 6) as Id_folio,  
           initialstate = @Tram,
        STUFF(@tram, 31, 6, 'nnnnnn') as returnstate

 

[yordangs]

thanks a lot thats what i need thanks a lot jbenson001 you save me from a headache

 

[jbenson001]

Glad to help

Jim