April 25, 2012 at 5:39 am
Hi all,
I have a function that is supposed to trim a column then remove any trailing space.
This function works if I parse the text directly but doesn't when I refer to a column in my table.
I will appreciate any help on this.
Here is the function:
CREATE FUNCTION [dbo].[CleanField]
(
@InputFieldRecord VARCHAR(Max)
)
RETURNS VARCHAR(Max)
AS
BEGIN
DECLARE @OutputFieldRecord VARCHAR(Max)
-- Trim Data
SET @OutputFieldRecord = LTRIM(RTRIM(@InputFieldRecord))
-- Double Spaces to single spaces
IF @OutputFieldRecord LIKE '% %' -- double spaces
BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, ' ', '')
END
RETURN @OutputFieldRecord
END
GO
and here is how I use it:
Select Distinct dbo.CleanField(Area) as 'clean field'
From MyTable
Order By 1
Result looks like this(which still has spaces as you can see):
clean field
'GAUTENG EAST 1 '
'GAUTENG EAST 2 '
I would also like to check the field for any funny characters that are not needed and remove them.
Thanks,
Scelo
April 25, 2012 at 6:20 am
Are you sure those are actually spaces? Trimming won't remove tabs, for example.
Try this and you'll see what I mean:-
select LEN(RTRIM('Test '))
Select LEN(RTRIM('Test'))
(In the first select there's a trailing space. In the second there's a trailing Tab)
As for removing 'Funny Characters' that's just a matter of deciding what constitutes a funny character and doing a replace. You can nest as many replaces as you like inside each other so you tend to end up with something like this:-
Select Replace(Replace(Replace(MyField, 'BugsBunny', ''), 'FrankSpencer', ''), 'AustinPowers', '')
Also, there's no real need to check that a string contains a double space before replacing it because the replace statement already does that implicitely (if there are no double spaces it simply won't do any work)
April 25, 2012 at 6:24 am
how would I cater for the tab or double space?
April 25, 2012 at 6:30 am
Oops, my edit crossed over with your post.
Assuming you want to get rid of it regardless of where it is in the string then just replace(MyField, 'Tab', '') (I've had to type tab in so it will go into a post, you would use an actual tab).
If you only want to remove it from the end that that's more complicated. I dopn't thik there's a straightforward command so you'd have to write a loop that examined the last character in your string and removed it if it was a tab then repeated until it found a non-tab. It'll be awkward and probably quite slow so hopefully someone can provide a better solution.
April 25, 2012 at 6:56 am
For the trailing spaces why not use the RTRIM function for example:
DECLARE @Area VARCHAR(50)
DECLARE @A2 VARCHAR(50)
SET @Area = 'GAUTENG EAST 1 '
SET @A2 = 'GAUTENG EAST 2 '
SELECT '*'+ RTRIM(@Area)+ '*','*'+RTRIM(@A2)+ '*'
Result:
*GAUTENG EAST 1* *GAUTENG EAST 2*
Now the '*' in the above is just to demonstrate that the trailing spaces have been removed and of course would not be used in your production code.
April 25, 2012 at 7:04 am
Hi bitbucket,
My function is already trimming the field but for some reason the trim did not work.
This leads me to believe that it is not a space but a funny character, now I do not know how to check for funny characters.
April 25, 2012 at 7:36 am
Hi all,
I find a solution for my problem.
It seems that there was line feed {char(10)} and that was looking like a space.
So I used:
REPLACE(@OutputFieldRecord, char(10), '')and this solved the problem.
I altered my function to cater for tab, line feed and carriage return just in case.
here it is in case someone has the same problem:
CREATE FUNCTION [dbo].[CleanField]
(
@InputFieldRecord VARCHAR(Max)
)
RETURNS VARCHAR(Max)
AS
BEGIN
DECLARE @OutputFieldRecord VARCHAR(Max)
SET @OutputFieldRecord = RTRIM(@InputFieldRecord) -- Trim Data
IF @OutputFieldRecord LIKE '% %' -- spaces
BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, ' ', '')
END
BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, char(9), '') --Tab
END
BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, char(13), '') -- Carriage return
END
BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, char(10), '') -- Line feed
END
RETURN @OutputFieldRecord
END
GO
Thanks for your replies.
April 25, 2012 at 7:40 am
You might still have problems with that function.
If, for example, you have a double-space inside the string, instead of just at the end of it, then you won't end up with a single-space, you'll end up with words crunched together.
Also, try it with a string with an odd number of trailing spaces. 3 or 5.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2012 at 7:55 am
Here is a general method to determine what that last character is.
[DECLARE @A2 VARCHAR(50)
SET @A2 = 'GAUTENG EAST 1'
--The @A2 has a horizontal tab as the last character
SELECT ASCII(SUBSTRING(@A2,LEN(@A2)-1,LEN(@A2))) AS 'ASCII Character value'
The above returns a value of 9 which is a horizontal tab.
April 25, 2012 at 8:06 am
Thanks,
So having a check for char(9), char(10) and char(13) should cover me for the spaces?
I have implemented the function on my full query and seems to be working fine so far.
April 25, 2012 at 8:18 am
I think the point is that you want to understand the difference between replacing and trimming and why you use each technique.
A Trim will remove leading or trailing spaces from a string. It will only work on spaces so it won't help you with tabs, line feeds etc. It will always deal with the beginning or end of the string (depending on whether you use LTrim or RTrim) and won't touch the middle.
A Replace will allow you to replace any known pattern of characters in a string with a different pattern of characters.
So typically, what I do when I want to clean a string is a bunch of replaces that remove any unwanted characters from the string. Once I've done that I trim it to get rid of any leading or trailing spaces. It's important to understand that they're two different operations, though, and that I'm using them for slightly differnet purposes.
They don't combine their functionality. You can't trivially TRIM trailing Tabs while leaving other Tabs in the middle of the string.
April 25, 2012 at 8:37 am
SceloM (4/25/2012)
Thanks,So having a check for char(9), char(10) and char(13) should cover me for the spaces?
I have implemented the function on my full query and seems to be working fine so far.
You might find it valuable to look at this site
and note that ASCII codes 1 thru 31 can/might also cause problems.
April 25, 2012 at 9:17 am
Not sure what this would do performance wise, but it works without affecting double spaces inside of a string.
DECLARE @Area VARCHAR(50);
DECLARE @A2 VARCHAR(50);
SET @Area = 'GAUTENG EAST 1 ';
SET @A2 = 'GAUTENG EAST 2 ';
SELECT '*'+ RTRIM(@Area)+ '*','*'+RTRIM(@A2)+ '*';
SELECT REVERSE(LTRIM(REVERSE(LTRIM(REPLACE(REPLACE(REPLACE(@Area,CHAR(13),''),CHAR(10),''),CHAR(9),''))))) +'*';
SELECT REVERSE(LTRIM(REVERSE(LTRIM(REPLACE(REPLACE(REPLACE(@A2,CHAR(13),''),CHAR(10),''),CHAR(9),''))))) +'*';
April 25, 2012 at 11:33 pm
Lynn Pettis (4/25/2012)
Not sure what this would do performance wise, but it works without affecting double spaces inside of a string.
DECLARE @Area VARCHAR(50);
DECLARE @A2 VARCHAR(50);
SET @Area = 'GAUTENG EAST 1 ';
SET @A2 = 'GAUTENG EAST 2 ';
SELECT '*'+ RTRIM(@Area)+ '*','*'+RTRIM(@A2)+ '*';
SELECT REVERSE(LTRIM(REVERSE(LTRIM(REPLACE(REPLACE(REPLACE(@Area,CHAR(13),''),CHAR(10),''),CHAR(9),''))))) +'*';
SELECT REVERSE(LTRIM(REVERSE(LTRIM(REPLACE(REPLACE(REPLACE(@A2,CHAR(13),''),CHAR(10),''),CHAR(9),''))))) +'*';
Thank you, I will try your method also see how it goes
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply