December 7, 2015 at 6:50 am
Hi,
I have a varchar column, DeliveryAddress, which, due to user-entry issues, occasionally ends up with values that have special characters at the end of it. This becomes a problem when we try to do grouping in our Crystal Reports. Therefore, I want to eliminate trailing special characters, such as CHAR(10) and CHAR(13).
So how can I achieve the update mentioned above?
I greatly appreciate any assistance!
December 7, 2015 at 7:51 am
tarr94 (12/7/2015)
Hi,I have a varchar column, DeliveryAddress, which, due to user-entry issues, occasionally ends up with values that have special characters at the end of it. This becomes a problem when we try to do grouping in our Crystal Reports. Therefore, I want to eliminate trailing special characters, such as CHAR(10) and CHAR(13).
So I have two questions:
1) How can I query my table in such a way as to not only see which records contain special characters, but also see what the special characters actually are?
2) How can I achieve the update mentioned above?
I greatly appreciate any assistance!
Do you know which "special" characters you are trying to deal with? Are these characters ONLY at the end or can the scattered throughout the text? If it is just the carriage return and line feed and they are only at the end a simple replace would do it. If it is more complex then you need to provide a lot more details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2015 at 8:24 am
Hi Sean. Thanks for responding.
After some more digging, I realize I no longer have a need for question #1. I've updated the initial post to reflect this.
I realize my only concern is, as you said, the carriage return and line feed. Do you have an example for how to do a replace of these characters at the end of the string value?
Thank you!
December 7, 2015 at 8:27 am
tarr94 (12/7/2015)
Hi Sean. Thanks for responding.After some more digging, I realize I no longer have a need for question #1. I've updated the initial post to reflect this.
I realize my only concern is, as you said, the carriage return and line feed. Do you have an example for how to do a replace of these characters at the end of the string value?
Thank you!
Again, are those ONLY at the end? Can they appear anywhere else in your string and you want to maintain them? It is more complicated if you only want to look at the last couple characters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2015 at 10:27 am
Yes. Only at the end.
December 7, 2015 at 10:39 am
tarr94 (12/7/2015)
Yes. Only at the end.
Not sure exactly what your answer is here. Do you need to maintain those characters other places in the string?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2015 at 10:45 am
This is how you would remove them only at the end:
DECLARE @table TABLE (col1 varchar(100));
INSERT @table VALUES ('address1'),('address2'+char(10)+char(13)),('address3');
SELECT
CASE
WHEN col1 LIKE '%'+char(10)+char(13)
THEN SUBSTRING(col1, 1, LEN(col1)-2)
ELSE col1
END
FROM @table;
-- Itzik Ben-Gan 2001
December 7, 2015 at 10:48 am
Thanks Alan!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply