get rid of square character in sql 2005

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

SSIS and Import/Export (2005)

get rid of square character in sql 2005

Author

Topic

kakashilee
Starting Member

1 Post

Posted - 2007-10-28 : 00:19:24

how to eliminate square character before the data.
When i import it from excel into sql server 2005, it have a double square character on my data. help.

Kristen
Test

22859 Posts

Posted - 2007-10-28 : 02:32:47

Use


SELECT CONVERT(varbinary(8000), MyColumn)
FROM MyTable
WHERE MyKey = 1234

to view what the actual rogue character is, and then use REPLACE to change it


DECLARE	@TestData TABLE
(
	MyColumn varchar(20)
)

INSERT	@TestData
SELECT	'1foo bar' UNION ALL
SELECT	'2foo' + CHAR(5) + N'bar'

SELECT	LEN(MyColumn), MyColumn, CONVERT(varbinary(20), MyColumn)
FROM	@TestData

UPDATE	@TestData
SET	MyColumn = 
	REPLACE(MyColumn,CHAR(5), 'XXXXXX')

SELECT	LEN(MyColumn), MyColumn, CONVERT(varbinary(20), MyColumn)
FROM	@TestData

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-28 : 02:33:40

NOTE: This will NOT work if the rogue character is CHAR(0) and the column is of Unicode datatype - i.e. Nvarchar

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources