Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Replace Duplicates - General Purpose UDF
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  


United Kingdom
22859 Posts

Posted - 02/15/2006 :  03:22:32  Show Profile  Reply with Quote
I've been having a look at Corey's code to replace multiple spaces with a single space:

Is there any reason why:

Replace(Replace(Replace(myCol, ' ', ' þ'), 'þ ', ''), 'þ', '')

should not have a DOUBLE expression in the first replace?

Replace(Replace(Replace(myCol, '  ', ' þ'), 'þ ', ''), 'þ', '')

otherwise I'm concerned that a) replacing a single space with ' þ' might cause an overflow and b) that I will be doing replaces of single instances where actually there is no work to do!

I was then planning to use my Tally table to find the first character not used in the string, and use that for the "marker", and thus make a general purpose "replace duplicates" UDF.

What's the most efficient way to do that?

SELECT TOP 1 @charMarker = CHAR(MyNumber)
FROM	dbo.MyTallyTable
WHERE	    mb_int_ID >= 65		-- Start at 'A' for this test
	AND CHARINDEX(CHAR(MyNumber), @strData) = 0

or should I use

	AND @strData NOT LIKE '%' + CHAR(MyNumber) + '%'

or something else?

Do I need to start at ASCII 65 ('A') or are the ASCII values 0-31 just fine?



Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 02/16/2006 :  06:22:34  Show Profile  Reply with Quote
I'm sure on the previous occasions where this method was posted here, the first search string did have two spaces in it. As you say, this prevents the possibility that the intermediate result will exceed 8000 characters.


Do I need to start at ASCII 65 ('A') or are the ASCII values 0-31 just fine?

They should be. They're only characters*, after all! I suspect the number of CHAR(0) characters in most databases is vanishingly small, and that most of them are indicative of errors in the software inserting them or that the column type should have been varbinary.

* Modulo (un)usual collation issues (aargh!)

  REPLACE(s COLLATE Latin1_General_CS_AS, CHAR(n), ''),
  CHARINDEX(s COLLATE Latin1_General_CS_AS, CHAR(n)),
  REPLACE(s COLLATE SQL_Latin1_General_Cp1_CS_AS, CHAR(n), ''),
  CHARINDEX(s COLLATE SQL_Latin1_General_Cp1_CS_AS, CHAR(n)),
  REPLACE(s COLLATE Latin1_General_BIN, CHAR(n), ''),
  CHARINDEX(s COLLATE Latin1_General_BIN, CHAR(n))
FROM (SELECT 'Test string' AS s) AS A, Numbers

Hmm, seems to only be CHAR(0) that behaves strangely (i.e. wrong)... now I wonder why that might be (bloody C programmers!)

Edited by - Arnold Fribble on 02/16/2006 06:38:15
Go to Top of Page


United Kingdom
22859 Posts

Posted - 02/16/2006 :  07:04:56  Show Profile  Reply with Quote
"bloody C programmers!"

I wonder how many different NULL jokes there are in the world!

"Modulo (un)usual collation issues (aargh!)"

Aargh indeed

Is ASCII 1-127, inclusive, "safe" then? and 128-255 potentially "dangerous"?

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000