This text should be varchar but it isn't.....

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 2000 Forums

Transact-SQL (2000)

This text should be varchar but it isn't.....

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-07-12 : 14:05:30

Not my application, but a text column is holding a short (~varchar(255)) description. The front end is including shifts/returns maybe null characters; not sure really. How can remove these from the "CASTed to varchar" column. LTRIM for example is leaving ""stuff"" at the front. Don't know what that ""stuff"" is, How can I find out?

QA shows 'results to text' as skipped lines or other
'results' to grid as white space of unknown origin

Kristen
Test

22859 Posts

Posted - 2004-07-12 : 14:15:50

SELECT ASCII(LEFT(MyColumn, 1))
FROM MyTable

will give you the ASCII code of the first character. If Nvarchar you'll need the first two characters. And so on!

Krsiten

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-07-12 : 14:55:10

The Rasheed Wallace of SQLTEAM.

Shows up, huge impact.

Thanks.

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-12 : 15:55:31

Who?
Anyway, tally tables are handy here too:

SELECT ASCII(SUBSTRING(MyColumn, n, 1)
FROM MyTable
INNER JOIN Numbers ON n BETWEEN 1 AND DATALENGTH(n)

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-07-12 : 16:48:52

Who?

sorry for the dribble Fribble and other UK members.

Rasheed Wallace, is an NBA player with a long history of a bad attitude, and a misfit, talented in the classic sense of the game.
Anyways this player was picked up mid/late season by my home team the Detroit Pistons. He fit right in, earned everybody's respect in a short time, worked hard and had fun doing it. Most of all he catalyzed the team into something they were not even close to being. NBA champs. It was an amazing thing to see.
Given the peotic licence of not knowing Kristen's past the rest fits as a metaphore from a fan's point of view.

Thanks for the tally table hint, that was a catalyst to.

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 02:00:16

I wish I had much more of a Mispent Youth to brag about ... but thanks for the comments, much appreciated, particularly given how many impolite visitors pass through these hallowed halls.

Arnaold's is a much more useful approach though. I think the following will find all non-printable characters (it shows the rouge character as [nnn] within the original string):


SELECT	n, 
	[char] = ASCII(SUBSTRING(MyColumn, n, 1)),
	SUBSTRING(MyColumn, 1, n-1) 
	+ '[' + CONVERT(varchar(3), ASCII(SUBSTRING(MyColumn, n, 1))) + ']' 
	+ SUBSTRING(MyColumn, n+1, DATALENGTH(MyColumn))
FROM	Numbers  
WHERE	n BETWEEN 1 AND DATALENGTH(MyColumn)
	AND
	(
		-- Only non-printable characters
		   ASCII(SUBSTRING(MyColumn, n, 1)) < 32 
		OR ASCII(SUBSTRING(MyColumn, n, 1)) > 127
	)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources