Puzzling Trailing Space Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Puzzling Trailing Space Issue

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-12-07 : 00:15:57

I've imported some records which have a trailing space. However, I'm unable to remove it with RTRIM(Lname) or REPLACE(lname, ' ', '') because SQL evidently doesn't recognize it as a space. However, you'll notice that the length of the last name field is off on records 2 - 5. The only unique way that I've been able to identify the records with this problem is that the corresponding VarBinary ends in "A0". Is there a way that I can use this information to filter these records and then simply use SUBSTRING(Lname, 0, LEN(Lname)) to remove the last space? I'd appreciate any suggestions for resolving this problem. Thanks!

ID VarBinary Lname LEN
1 0x426F7964 Boyd 4
2 0x4B656C6C79A0 Kelly 6
3 0x436F6C6C696E73A0 Collins 8
4 0x5368656C646F6EA0 Sheldon 8
5 0x43656368A0 Cech 5

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 00:25:12

Try this:

Update Tbl
Set LName = Replace(LName, char(160), '')

From your example data, it seems that varbinary contains binary representation of the LName contents. So, LName in record 2 and 5 contains non-printing ascii character (160).

Harsh Athalye
India.
"Nothing is Impossible"

ackweb
Yak Posting Veteran

54 Posts

Posted - 2006-12-07 : 01:45:41

This worked perfectly. Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 02:48:18

What if you have "hard spaces" inside the name?

Update Tbl
Set LName = rtrim(Replace(LName, char(160), ' '))

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 02:59:29

That was not in the original requirement!

Your solution assumes there will be trailing hard spaces, what if they are within the name?

Update Tbl
Set LName = Replace(Replace(LName, char(160), ''),' ','')

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:09:37

[code]DECLARE @s VARCHAR(100)

SELECT @s = CHAR(160) + 'Peter' + CHAR(160) + CHAR(160) + 'Larsson'

SELECT '_' + @s + '_',
'_' + REPLACE(LTRIM(RTRIM(REPLACE(@s, CHAR(160), ' '))), ' ', ' ') + '_' [Peso],
'_' + REPLACE(REPLACE(@s, CHAR(160), ''),' ','') + '_' [Harsh][/code]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 04:00:14

That's correct!

But I assume LName to be LastName and LastName generally doesn't contain space characters. But nothing can be guaranteed when we have to deal with bad data.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 04:15:26

What about Dutch last name 'de Groat'? Perfectly valid last name.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-07 : 04:29:44

Okay!

In that case your second solution is pretty neat than mine.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 05:09:37

<<
I'm unable to remove it with RTRIM(Lname) or REPLACE(lname, ' ', '') because SQL evidently doesn't recognize it as a space
>>

What is the datatype for that column?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources