SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Kristen
Test

United Kingdom
22403 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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195

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?

Thanks

Kristen

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.

quote:

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!)


SELECT n,
  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
WHERE n BETWEEN 0 AND 31
ORDER BY n


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

Kristen
Test

United Kingdom
22403 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"?

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