Suppressing new lines in a column of the db

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

Transact-SQL (2005)

Suppressing new lines in a column of the db

Author

Topic

josh777
Starting Member

12 Posts

Posted - 2007-06-29 : 16:11:01

Hi all,
I am trying to suppress new lines in a particular column of the database.
Column(address)

While i write a query and display the data, the result comes as

91 Kettle Lane,
Medford,
New York-11763

I need the output as
91,Kettle Lane,Medford,New York-11763

I am using vb.net. I tried the replace function it doesnt work.

I am usng this query rite now but it doesnt help.
SELECT replace(Address,'\n','') as Addr FROM ORDR WHERE CardCode ='C70002'

Is there any way i can suppress it with a query..?

Thanks,
Josh

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-29 : 16:15:12

Try:[CODE]SELECT REPLACE(REPLACE(Address, CHAR(13), ''), CHAR(11), '') as Addr FROM ORDR WHERE CardCode ='C70002'
[/CODE]
I think that should remove the CR+LF, assuming that is what you have. :)

Also, depending on your requirements, I'd suggest you remove those character(s) before inserting or let the front-end deal with them. (meh).

EDIT: Actualy you could combine them togther. I forget which order they go in, but something like:

SELECT REPLACE(Address, CHAR(13) + CHAR(11), '') as Addr FROM ORDR WHERE CardCode ='C70002'

josh777
Starting Member

12 Posts

Posted - 2007-06-29 : 17:13:11

Thanks Yak!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 23:57:35

"\n" should work in front end only

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-30 : 00:34:03

quote:
Originally posted by madhivanan

"\n" should work in front end only

Madhivanan

Failing to plan is Planning to fail

Not true in all cases apparently. In this thread I was suggesting to use char(10) as the rowterminator in a bulk insert statement, but it caused a syntax error. but '\n' worked. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38018

i admit I didn't try it before posting

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-30 : 01:08:56

quote:
Originally posted by jezemine

quote:
Originally posted by madhivanan

"\n" should work in front end only

Madhivanan

Failing to plan is Planning to fail

Not true in all cases apparently. In this thread I was suggesting to use char(10) as the rowterminator in a bulk insert statement, but it caused a syntax error. but '\n' worked. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38018

i admit I didn't try it before posting

elsasoft.org

Well. I meant that it will never work in sql with Replace function

Madhivanan

Failing to plan is Planning to fail

tariq.younas
Starting Member

1 Post

Posted - 2007-07-10 : 08:27:25

hi Josh and Madhivanan,
that would me like give below

Correct Query
-------------
SELECT replace(Address,char(13)+char(10),'') as Addr FROM ORDR WHERE CardCode ='C70002'

instead of char(13)+char(11)

Regards,
Tariq Younas
Software Engineer
Descon Information Systems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources