replace NOT working!!! - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

replace NOT working!!!

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2008-06-24 : 16:25:14

hi, I'm trying to get rid of the intermediate double space from the following string:

DOC-#911585 P.O.-Box-41

so I do:

select replace('DOC-#911585 P.O.-Box-41',' ','-')
and it yields:

DOC-#911585--P.O.-Box-41

BUT

if I do:
select replace(fieldFrommyTable,' ','-') from myTable
where myaddressVal='DOC-#911585 P.O.-Box-41'

SQL Server does nothing with the double white space...
the double space remains, what am I missing?

HELP!

thank you

heze
Posting Yak Master

192 Posts

Posted - 2008-06-24 : 16:39:57

sorry the string is:

'DOC-#911585 P.O.-Box-41'

the double space is between the 5 and the P, I still can't find a solution , checked a couple of pages including:
http://www.xaprb.com/blog/2005/11/15/a-bug-in-microsoft-sql-servers-replace-function/
but havent found a solution...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 16:48:13

Are you really sure it is a space (ascii 32, soft space) and not ascii 160 (hard space)?

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-24 : 17:28:06

It is helpful to convert your string to varbinary to look for hidden or unusual characters.

select MyString = convert(varbinary(30),'DOC-#911585 P.O.-Box-41')

Results:
MyString
-------------------------------------------------------------- 
0x444F432D2339313135383520502E4F2E2D426F782D3431

(1 row(s) affected)

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2008-06-25 : 09:08:47

Thanks guys I found the problem: I failed to test for other special characters and the culprits were
Line feed
char(10)

Carriage return
char(13)

How can you insert a carriage return in a db field!!???
There were no hard spaces Peso...

I went to BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/955afe94-539c-465d-af22-16ec45da432a.htm

then just tested by brute force:

select replace([name],char(9),'*') from #e
select replace([name],char(10),'*') from #e
select replace([name],char(13),'*') from #e

and the first special character was 10 andthe second one 13,
this is the reason for which when I bcp my table toa text file, there were several incomplete lines,
took me 2 days to figure...hope this is useful to someone - Look for the special characters!

Michael,
this is my result when I apply the convert(varbinary(30),[myField]):

0x444F4320233931313538350D0A502E4F2E20426F78203431202020202020

where would you find the char(10) and char(13)?

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:09:59

0x0D0A

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:12:45

At least we pointed you the the right direction.
And the reason why you didn't spot the CrLf is probably that you use GRID MODE and not TEXT MODE in query window.

DECLARE	@b AS VARBINARY(200)

SET	@b = 0x444F4320233931313538350D0A502E4F2E20426F78203431202020202020

SELECT	SUBSTRING(@b, Number, 1) AS Binary,
	ASCII(SUBSTRING(@b, Number, 1)) AS ASCII,
	CHAR(ASCII(SUBSTRING(@b, Number, 1))) AS Character
FROM	master..spt_values
WHERE	Type = 'p'
	AND Number BETWEEN 1 AND DATALENGTH(@b)

Result is

Pos	Binary	ASCII	Character
---	------	-----	---------
  1	0x44	68	D
  2	0x4F	79	O
  3	0x43	67	C
  4	0x20	32	 
  5	0x23	35	#
  6	0x39	57	9
  7	0x31	49	1
  8	0x31	49	1
  9	0x35	53	5
 10	0x38	56	8
 11	0x35	53	5
 12	0x0D	13	 
 13	0x0A	10	 
 14	0x50	80	P
 15	0x2E	46	.
 16	0x4F	79	O
 17	0x2E	46	.
 18	0x20	32	 
 19	0x42	66	B
 20	0x6F	111	o
 21	0x78	120	x
 22	0x20	32	 
 23	0x34	52	4
 24	0x31	49	1
 25	0x20	32	 
 26	0x20	32	 
 27	0x20	32	 
 28	0x20	32	 
 29	0x20	32	 
 30	0x20	32

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-25 : 09:45:45

quote:
Originally posted by heze
...0x444F4320233931313538350D0A502E4F2E20426F78203431202020202020

where would you find the char(10) and char(13)?...

There are still people that can't read read ascii directly?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 09:49:08

Where is the world going to?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources