Hi Lynn,

What if the source data contained the same pattern that is repeated many times? Example:

SELECT 1,'001N data 002N003N004N','data' UNION

SELECT 2,'005N005Ndata007N','data' UNION

SELECT 3,'data 008N009N010N','data ' UNION

SELECT 4,'data011Nsome012Nmore013Ndata014N','datasomemoredata' UNION

SELECT 5,'data015N some 016N017Nmore data','data some more data' UNION

SELECT 6,'data 050Nsome more 051N052Ndata','data some more data' UNION

SELECT 7,'data 090N some more data091N092N','data some more data' UNION

SELECT 8,'data093Nsome more 094N095Ndata','datasome more data' UNION

SELECT 9,'data096N some more097N098N data','data some more data' UNION

SELECT 10, 'data099N 4 some100N 5more 101Ndata','data 4 some 5more data';

Note that in my example, the string I want to remove is always 3 characters long, datatype Text, in the format of [0-9][0-9][0-9]N, ie. 001N, 002N, ... 999N, which repeats up to 999 times in each string, and no numbers repeat, they just increment upwards by 1. In addition, eliminating all but 1 space in between all words would also be helpful too. Thanks!

EDIT: I've solved the problem myself by creating a function as follows:

CREATE FUNCTION [dbo].[fnCleanN]

(

@BadString nvarchar(MAX)

)

RETURNS nvarchar(MAX)

AS

BEGIN

DECLARE @npos INTEGER

SELECT @npos = PATINDEX('%[0-9][0-9][0-9]N%', @BadString)

WHILE @npos > 0

BEGIN

SELECT @BadString = STUFF(@BadString, @npos, 4, '')

SELECT @npos = PATINDEX('%[0-9][0-9][0-9]N%', @BadString)

END

RETURN @BadString

END