Replace carriage return - 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 2000 Forums

Transact-SQL (2000)

Replace carriage return

Author

Topic

galbrecht
Starting Member

17 Posts

Posted - 2008-02-20 : 14:09:08

Hi

I have a text field that I would like to replace the carriage return with a space or ** when selecting the data.
Can anyone help me

Thanks

Greg

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 14:36:29

replace(YOURFIELD, char(13), ' ')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

galbrecht
Starting Member

17 Posts

Posted - 2008-02-20 : 18:16:22

Thanks jhocutt

When doing this I get the error

Server: Msg 8116, Level 16, State 1, Line 9
Argument data type text is invalid for argument 1 of replace function.

the field is currently a text field can I convrt it to a varchar during the select statement?

Greg

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-21 : 01:07:01

See what Select max(datalength(col)) from table returns
Then you can do

select replace(substring(col,1,8000),char(13),'') from yor_table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources