Return Text data type as Varchar in Select..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Return Text data type as Varchar in Select..?

Author

Topic

tito_puente44
Starting Member

7 Posts

Posted - 2006-06-07 : 16:27:34

Hi all,

Hoping someone can help me as i've hit a wall... i need to run a simple query and return varchar data type from a text column.. my problem is that when i return data from a text column, with it also it returns text formatting etc, and i don't know how to handle it when i just need the varchar (or string) format...

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 16:51:09

Even varchar retains the formatting, so converting it to varchar will not remove the formatting. Check this out:

DECLARE @s varchar(100)

SET @s = 'Tara is the best.
She likes SQLTeam.com.'

PRINT @s

So you'll want to return the text to your application, then massage the data to what you want.

Tara Kizer
aka tduggan

tito_puente44
Starting Member

7 Posts

Posted - 2006-06-07 : 17:18:01

Hey thanks for the reply!

You're right about varchar containing formatting. I just tried doing this

SELECT CAST(my_text_data_type AS VARCHAR(200)) WHERE ...

and got the same result.

I'm not sure how to incorporate what you're suggesting though, I'm not very proficient in SQL.

:/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-07 : 17:24:17

I'm suggesting to not use T-SQL to get rid of the formatting. Use your application to do that.

Tara Kizer
aka tduggan

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-06-07 : 18:06:54

I aggree with Tara on this. You should control the data comming into your database, or change the formatting at the presentation tier.

If you must do it in T-SQL here's one way:


DECLARE @MyTextField VARCHAR(8000)

SELECT @MyTextField = 'Hi!
My name is Michael.

I like
	Yaks
	Cars
	Money
That should be a good enough test!!'

select REPLACE(REPLACE(REPLACE(@MyTextField, CHAR(9), ''), CHAR(10), ' '), CHAR(13), '')

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

tito_puente44
Starting Member

7 Posts

Posted - 2006-06-07 : 19:28:35

Thanks for the reply!

The problem is that I have no control over the information input or the database structure, as I am building an API to tie into a fully blown in production CRM application that currently supports roughly 130 users.

Michael, I can see how you are going about the problem, but there are just too many variables, with bold letters, italics, different fonts, etc. that are stored in the table as text data type.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources