5/3/2024 1:30:47 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Removing Carriage returns and new lines.
Posted: 09 Oct 09 9:32 AM
|
| I have been attempting to use the REPLACE statment to remove CHAR(13), etc in my SELECT statment. Everything I try and do returns a null. I even tried to SET the variable REPLACE(@list, char(13), ' ') Any Ideas? |
|
|
|
Re: Removing Carriage returns and new lines.
Posted: 09 Oct 09 9:45 AM
|
Do you have an specific example?
This works fine for me.
Check the code below, and try it. Make sure that you are setting "Result to Text" if using Query Analyzer/Management Studio
DECLARE @LIST AS varchar(32)
SET @LIST = 'LINE1' + CHAR(13) + 'LINE2'
SELECT @LIST
/* Returns:
--------------------------------
LINE1
LINE2
(1 row(s) affected)
*/
SELECT REPLACE(@list, CHAR(13), '')
/* Returns:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LINE1LINE2
(1 row(s) affected)
*/ |
|
|
|
Re: Removing Carriage returns and new lines.
Posted: 16 Oct 09 10:31 AM
|
I think I am getting 2 logical errors. One from the CHARVAR and the other from the DECLARE. By making this CHARVAR(250) instead of just CHARVAR the output goes from truncating after 30 characters to giving partial output. When I use the REPLACE with CHARVAR it returns NULL. When I use the REPLACE with the CHARVAR(250) it gives me a partial output.
CREATE FUNCTION sysdba.GetActivityListForTicket005(@ticketid varchar(12)) returns varchar(8000) AS BEGIN DECLARE @list varchar(8000)
SET @list = ''
SELECT @list = @list + CONVERT(varchar, a.createdate, 1) + ' ' + DECLARE(cast(a.activitydesc AS varchar(250)), CHAR(13), '') + ', '
FROM sysdba.ticketactivity a
WHERE a.ticketid = @ticketid
ORDER BY a.createdate
SET @list = rtrim(@list) IF @list <> ''
SET @list = LEFT(@list, len(@list) - 1) RETURN @list END |
|
|
|
Re: Removing Carriage returns and new lines.
Posted: 16 Oct 09 11:04 AM
|
Why don't you assign the values into the Varialbe first and then do a Replace on it:
SELECT @list = CONVERT(varchar, a.createdate, 1) + ' ' + LTRIM(RTRIM(cast(a.activitydesc AS varchar(7980))))
FROM .....
Then:
Set @list = Replace(@list, char(13), '')
Set @list = Replace(@list, char(10), '')
|
|
|
|
Re: Removing Carriage returns and new lines.
Posted: 16 Oct 09 11:42 AM
|
| I tried this and it returned null on my test ticket but it was working for some of the other tickets. So I closed my test ticket and opened a new one with new activities. It outputed everything but I could still see the carriage returns that I put in the activites. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|