Enclose data type text in double quotes

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)

Enclose data type text in double quotes

Author

Topic

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 09:07:27

In a query I want to enclose my results in double quotes. Normally I would do this like:

select '"'+column+'"' from table

or (in case of an int)

select '"'+ cast(column AS char) +'"' from table

How can I do this for a column with the data type text?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:10:11

See if this helps you
http://mindsdoor.net/SQLTsql/ReplaceText.html

Madhivanan

Failing to plan is Planning to fail

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 09:23:31

hmmm don't see how this would help me. I do not want any charcaters replaced I want to add to characters. If a record in the column with data type text would contain:

How can I get this to work + more text! ?

I want a grid result looking like

"How can I get this to work + more text! ?"

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-17 : 10:00:28

CREATE MyProc
@column varchar(10)
AS

DECALRE @sql as varch(100)

SET @sql = 'select ' + @column + ' from table'

EXEC(@sql)

I shy away from dynamic sql but this should work. If not, you may want to take a glance at sp_executesql

====================================================
Regards,
Sean Roussy

Thank you, drive through

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 10:07:23

Thrasymachus,

Will this work for Text column?

>>In a query I want to enclose my results in double quotes.

The quetioner wants text column to be within double quotes

Madhivanan

Failing to plan is Planning to fail

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 10:07:52

I was under the assumptions that varchar was limited to 8000 chararcters. Sometimes the text typed is several pages. I think that using CAST could do the same thing as the script above or am I mistaken?

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-17 : 10:24:37

missed the text column requirement. Sometimes I just scan the post.

On a side note, TEXT is too often abused and rarely needed when it is used. I am about to have this discussion with my developers. Doing something like Select MAX(LEN(CAST(myText as nvarchar(4000)))) FROM MyTable usually makes my point with them about this after their apps have been in production for a while.

====================================================
Regards,
Sean Roussy

Thank you, drive through

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 10:28:10

So the answer is

Select '"'+CAST(myText as nvarchar(4000))+'"' FROM MyTable

Madhivanan

Failing to plan is Planning to fail

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 10:32:41

tested it against a functional database with As varchar(8000)

result 8000 so I need the data type TEXT

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-17 : 10:35:10

by the way my last post refers to:

Select MAX(LEN(CAST(column as varchar(8000)))) FROM table