October 17, 2008 at 11:04 am
Hi guys,
This is driving me crazy, I have to trim off blank space in a table, but it just seems not working.
I have a table, with a field varchar(50), the content in that field varies off-course, but when I
select len(ltrim(rtrim(field))) from table
it returns 50 for all records.
What's going on there? I don't believe there is a bug in SQL, but this scenario is really like a bug to me.
Any one has the similar problem? What should I do?
Thanks.
October 17, 2008 at 11:21 am
Hi,
When you use Len(String), it returns the length of the string before it finds only blanks. So a string with 2 blanks and 4 characters returns a length of 6, and a string of 2 blanks, 4 characters, and 2 other blanks still return a length of 6. Maybe the string you were trimming did not have leading blanks?
Here is a quick example of trimmin and length:
USE tempdb
GO
CREATE TABLE tempData
(
stringToTrim NVARCHAR(50)
)
INSERT INTO tempData
Select 'abc '
UNION ALL
SELECT ' BC E '
UNION ALL
SELECT ' 1SpaceThen3Spaces '
SELECT StringToTrim,
LTRIM(RTRIM(StringToTrim)) AS TrimmedString,
LEN(StringToTrim) AS LenStringToTrim,
LEN(Ltrim(RTRIM(StringToTrim))) AS LenTrimmedString
FROM tempdata
DROP TABLE tempData
GO
SELECT LEN('ABC')
UNION ALL
SELECT LEN('ABC ')
UNION ALL
SELECT LEN(' ABC')
UNION ALL
SELECT LEN(' ABC ')
You can see that only the leading spaces trimmed reduce the length of the string, because len() already trims the trailing spaces to calculate the length.
Hope this helps,
Sincerely,
Cheers,
J-F
October 17, 2008 at 11:24 am
Here is my screenshot
October 17, 2008 at 11:33 am
Works perfectly for me, are you sure the string is the right type? Why is there 49 length values and then 50? It works correctly for the NULL saying 4. See my screenShot.
Cheers,
J-F
October 17, 2008 at 11:37 am
Try checking for special characters like Tab and Carriage Return, they are not removed by the TRIM functions only blanks (spaces) are.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 11:52 am
Sorry I don't get you guys. As you can see from my screenshot, it is so OBVIOUS that length should be different for each record.
October 17, 2008 at 12:12 pm
With your screenshot, we can not see if there is blank at the right, like carriage return, tab or space.
October 17, 2008 at 12:17 pm
No, it actually is not obvious. If the non-visible characters in your string are TABS then the TRIM functions WILL NOT trim them so the length will remain the same. Try this:
[font="Courier New"]DECLARE @test TABLE(id INT, string VARCHAR(10))
INSERT INTO @test
SELECT
1,
'abc' --3 chars
UNION ALL
SELECT
2,
'abc ' -- 1 space 4 chars no trim 3 chars with trim
UNION ALL
SELECT
3,
'abc ' -- 2 spaces 5 chars
UNION ALL
SELECT
4,
'abc' + CHAR(9) -- 1 tab 4 chars
UNION ALL
SELECT
5,
'abc' + CHAR(9) + CHAR(9) -- 2 tabs 5 chars
SELECT
id,
string,
LEN(string) AS length,
LEN(RTRIM(string)) AS trim_length,
-- replace tabs with spaces
LEN(RTRIM(REPLACE(string, CHAR(9), ' '))) AS no_tabs_trim_length
FROM
@test
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 12:27 pm
OK, so it's not OBVIOUS.
Then how do I find out the special characters in my records?
Thanks.
October 17, 2008 at 12:36 pm
I checked char(9), char(10), char(13), none of them exist in my records.
select charindex(char(10), techowner_department) from epm where techowner_department is not null
October 17, 2008 at 12:41 pm
I'd pull out a sample of the strange rows, and then convert each byte to its ASCII value. From there you might see some pattern that is causing issues and set up removal for it.
Might be an SSIS way to clean this easily as well, run it out and through SSIS and then back in.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 17, 2008 at 12:49 pm
- What's your ANSI padding set to ?
- use datalength in stead of LEN !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2008 at 12:54 pm
Thanks for your hint, that's what I am trying. Can you provide more instruction on SSIS?
I did the following in Access:
I export the table to mdb, I tried to update the field using trim in access:
UPDATE EPM SET EPM.Test = LTrim(RTrim("TechOwner_Department"));
Unfortunately it is not working, all records have been update to the same content. Is the query wrong? How can I update records in its original place in Access? Is there a cursor like function in Access? so that I can pull out the record one by one and update it.
Thanks.
October 17, 2008 at 1:03 pm
halifaxdal (10/17/2008)
Thanks for your hint, that's what I am trying. Can you provide more instruction on SSIS?I did the following in Access:
I export the table to mdb, I tried to update the field using trim in access:
UPDATE EPM SET EPM.Test = LTrim(RTrim("TechOwner_Department"));
Unfortunately it is not working, all records have been update to the same content. Is the query wrong? How can I update records in its original place in Access?
Thanks.
Actually it is working !
You did not use a where clause, so all your rows should have the value "TechOwner_Department" for column Test !
btw some test results for datalength:
/*------------------------
set ansi_padding off
go
DECLARE @test-2 TABLE(id INT, string VARCHAR(10))
INSERT INTO @test-2
SELECT
1,
'abc' --3 chars
UNION ALL
SELECT
2,
'abc ' -- 1 space 4 chars no trim 3 chars with trim
UNION ALL
SELECT
3,
'abc ' -- 2 spaces 5 chars
UNION ALL
SELECT
4,
'abc' + CHAR(9) -- 1 tab 4 chars
UNION ALL
SELECT
5,
'abc' + CHAR(9) + CHAR(9) -- 2 tabs 5 chars
SELECT
id
, string
, LEN(string) AS LEN_length
, LEN(RTRIM(string)) AS trim_length
-- replace tabs with spaces
, LEN(RTRIM(REPLACE(string, CHAR(9), ' '))) AS no_tabs_trim_length
, datalength(RTRIM(string)) AS _datalength_trim_string
, datalength(string) AS datalength_string
FROM
go
set ansi_padding ON
go
DECLARE @test-2 TABLE(id INT, string VARCHAR(10))
INSERT INTO @test-2
SELECT
1,
'abc' --3 chars
UNION ALL
SELECT
2,
'abc ' -- 1 space 4 chars no trim 3 chars with trim
UNION ALL
SELECT
3,
'abc ' -- 2 spaces 5 chars
UNION ALL
SELECT
4,
'abc' + CHAR(9) -- 1 tab 4 chars
UNION ALL
SELECT
5,
'abc' + CHAR(9) + CHAR(9) -- 2 tabs 5 chars
SELECT
id
, string
, LEN(string) AS LEN_length
, LEN(RTRIM(string)) AS trim_length
-- replace tabs with spaces
, LEN(RTRIM(REPLACE(string, CHAR(9), ' '))) AS no_tabs_trim_length
, datalength(RTRIM(string)) AS _datalength_trim_string
, datalength(string) AS datalength_string
FROM
------------------------*/
(5 row(s) affected)
id string LEN_length trim_length no_tabs_trim_length _datalength_trim_string datalength_string
----------- ---------- ----------- ----------- ------------------- ----------------------- -----------------
1 abc 3 3 3 3 3
2 abc 3 3 3 3 3
3 abc 3 3 3 3 5
4 abc 4 4 3 4 4
5 abc 5 5 3 5 5
(5 row(s) affected)
(5 row(s) affected)
id string LEN_length trim_length no_tabs_trim_length _datalength_trim_string datalength_string
----------- ---------- ----------- ----------- ------------------- ----------------------- -----------------
1 abc 3 3 3 3 3
2 abc 3 3 3 3 4
3 abc 3 3 3 3 5
4 abc 4 4 3 4 4
5 abc 5 5 3 5 5
(5 row(s) affected)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2008 at 1:06 pm
How do I check ANSI Padding setting in my server? Why it seems working differently on my two tables with same (looks same) records?
I have another table in the same database, I have no problem with that table.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply