Problem with Query

I need to replace the text (III|xII) with (1II|xII)'. For that i created the following query.

SELECT (REPLACE((STUFF(Col1,1,1,'1')),'I','1')) FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

The problem with this query is that it replaces all '|' with '1'. ie, it gives (111|x11).

Could anyone ple show me what's wrong with the query.

All help appreciated.

Thanks,

The following code will give you the flexibility to change any char in the string, at any position, at any length. This function returns max 15 chars (but that’s easy to change)…

CREATE FUNCTION dbo.FixString
(
@.DataString varchar(15),
@.StartPosition smallint,
@.StartLength smallint,
@.SeacrhString varchar(15),
@.ReplaceString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @.Part1 varchar(15),
@.Part2 varchar(15),
@.Part3 varchar(15)

SET @.Part1 = SUBSTRING(@.DataString, 1, @.StartPosition - 1)
SET @.Part2 = REPLACE(SUBSTRING(@.DataString, @.StartPosition, @.StartLength), @.SeacrhString, @.ReplaceString)
SET @.Part3 = SUBSTRING( @.DataString, @.StartPosition + @.StartLength, LEN(@.DataString) - (@.StartPosition + @.StartLength) + 1 )

RETURN @.Part1 + @.Part2 + @.Part3
END
GO

SELECT dbo.FixString(Col1,1,1,'B','1') FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

Happy SQL'n,
Kent Howerter

|||You don't need the REPLACE since it will replace all occurrences of the specified string. STUFF is enough in this case since you are using the length parameter to delete the character at specified position and insert the new one.