Ignoring Character Returns

[lasd]

Hi

I am having a problem with character returns in SQL. I am extracting data from a database and one of the address colunns that i am extracting the data from contains character returns within the text. this is throwing my results out of sync and I cannot see the end of some of the address. Does anyone know if there is any way that I can ignore the character returns or skip over them.

Many thanks in advance for your help.
Kindest Regards
Lasd

 

[DotNetGnat]

you just need to replace spaces and tabs using

Replace() function....

-DNG

 

[lasd]

Thanks for that reply... i was trying to use the replace function yesterday but i am not sure how to represent the character return in the replace function. for eg

replace(profile.abstract,',','')
this will replace the comma's within the address but how do i ask it to look for the character return(which is represented by a little box) in the address

thanks in advance
lasd

 

[DotNetGnat]

Try these:

Replace(profile.abstract,vbcrlf,'')

Replace(profile.abstract,chr(10),'')

Replace(profile.abstract,chr(13),'')


-DNG

 

[lasd]

Hi

Thanks for that, that is working but one question.

select replace(profile.abstract,CHR(10),'')||' '|| TRIM(components.path)


Above is the code I am using to replace chr(10)with a space. This is working fine but i also want to get rid of the chr(13) from this string profile.abstract. I can't concatenate another replace statement as this will just add in the address again without the character return. so the address would then be repeated twice.
do you know how i would tackle this?
thanks in advance

lasd

 

[DotNetGnat]

use nested replace statements...

Select replace(replace(profile.abstract,chr(10),''),chr(13),'')

-DNG

 

[lasd]

Thanks so much.
this works perfectly.

many thanks

Lasd

 

[DotNetGnat]

no problem...glad to be of help....

-DNG