SELECT TOP 1 @charMarker = CHAR(MyNumberColumn)
FROM	dbo.MyTallyTable
WHERE	    MyNumberColumn >= 33	-- Normal AlphaNumericPunctuation range
	AND MyNumberColumn <= 127
	AND CHARINDEX(CHAR(MyNumberColumn), @strData) = 0

declare @CHR		varchar(2)
declare @UNIQ		varchar(2)
Set @CHR = 'b'
Set @UNIQ = 'Þð' (0222,0240)

Select 
	data,
	replace(data,replicate(@CHR,2),@UNIQ),
	replace(replace(data,replicate(@CHR,2),@UNIQ),reverse(@UNIQ),''),
	replace(replace(replace(data,replicate(@CHR,2),@UNIQ),reverse(@UNIQ),''),@UNIQ,@CHR),
	replace(replace(replace(replace(data,replicate(@CHR,2),@UNIQ),reverse(@UNIQ),''),@UNIQ,@CHR),replicate(@CHR,2),@CHR)
From
	(
	Select data='abba' Union All
	Select 'abbba' Union All
	Select 'abbbba' Union All
	Select 'abbbbba' Union All
	Select 'abbbbbba' Union All
	Select 'abbbbbbba' Union All
	Select 'abbbbbbbba'
	) a

quote:

---

Originally posted by Kristen
...but it does need consideration of the collation issues ...

---

quote:

---

Originally posted by Seventhnight

do you actually care how many spaces there are... or do you just want to force there to be just one?

if its the latter... a series of 3 replaces will do the trick...

I usually use (in SQL)

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

so in access it should be

Replace(Replace(Replace(myCol," "," þ"),"þ ",""),"þ","")

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

---

quote:

---

Originally posted by X002548

Maybe something like

DECLARE @x varchar(8000)
SELECT @x = 'Brett  J   Kaiser'
SELECT REPLACE(REPLACE(@x,'  ',' '),'  ',' ')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

quote:

---

Originally posted by gongxia649
...what is this character "þ"?

---