can't get rid of leading zeroes

Question

can't get rid of leading zeroes

polkadot

SSChampion

Points: 13664
More actions
October 21, 2013 at 3:31 pm

#299483

have this problem where I've got a realname field (varchar (255)) where the front end doesn't enforce naming practices and allows leading zeroes. So sometimes the same name will end up in the database, in the same column, with leading zeros and sometimes not. I need to remove leading zeros from all names so that I can group by the realname.
Seems pretty simple to use LTRIM(RTRIM(expression)), but surprised to find the leading zeroes remain. I know they've remained because when I use the LEN() function against one of the offending names to show me how many characters long it is longer then that of the same name in another row. Plus, when I filter for name without the % wildcard, only the one returns.
Thought this would be simple ltrim/rtrim solution. Anyone have understanding of why I'm having this problem?
--Quote me

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

LTRIM and RTRIM only remove leading and trailing white space. It doesn't remove zeros. If this is a name field are zeros allowed at all? If not, then try REPLACE() and replace all 0's with an empty space ''

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

polkadot SSChampion Points: 13664 More actions · Answer 2

ach, sorry, I meant leading white spaces. NOT zeroes.

like this:

/****** Script for SelectTopNRows command from SSMS ******/

DECLARE @REALNAME varchar(255)

SET @REALNAME = ' Ricki Ricardo'

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

in this example the ltrim/rtrim works as expected. But for my real life scenario, the leading white spaces aren't getting removed. It's as though the blanks are something other than leading white spaces....but what could they be?

--Quote me

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

You may have leading and/or trailing tabs instead of spaces which the trim's won't replace. You can use CHARINDEX to find CHAR(9) in a string and then use the replace function if that is the issue.

/****** Script for SelectTopNRows command from SSMS ******/

DECLARE @REALNAME varchar(255)

SET @REALNAME = ' Ricki Ricardo'

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

SET @REALNAME = 'Ricki Ricardo'

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

polkadot SSChampion Points: 13664 More actions · Answer 4

I see what you're saying. I tried it with the below example, and it returned 1 for placevalue of first tab.

DECLARE @REALNAME varchar(255)

SET @REALNAME = 'Ricki Ricardo'--where the leading space is a single tab space

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

select charindex(char(9),@realname, 0)

But, in my case it's not a tab. The position value returned was 0. What other character could the white space be?

--Quote me

polkadot SSChampion Points: 13664 More actions · Answer 5

I tried to see if converting to binary would expose a character type, but the beginning of binary out looks same in both cases.

DECLARE @REALNAME varchar(255)

SET @REALNAME = 'Ricki Ricardo'---leading space is a tab

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

select charindex(char(9),@realname, 0)

select convert(binary(15), @realname)--yields 0x095269636B69205269636172646F00

SET @REALNAME = ' Ricki Ricardo'

select LEN(@realname)

select len(RTRIM(ltrim(@realname)))

select charindex(char(9),@realname, 0)

select convert(binary(15), @realname)--yields 0x205269636B69205269636172646F00

--Quote me

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Keith Tate (10/21/2013)
You may have leading and/or trailing tabs instead of spaces which the trim's won't replace. You can use CHARINDEX to find CHAR(9) in a string and then use the replace function if that is the issue.
/****** Script for SelectTopNRows command from SSMS ******/
DECLARE @REALNAME varchar(255)
SET @REALNAME = ' Ricki Ricardo'
select LEN(@realname)
select len(RTRIM(ltrim(@realname)))
SET @REALNAME = 'Ricki Ricardo'
select LEN(@realname)
select len(RTRIM(ltrim(@realname)))

CHAR(160)... the notorious and dreaded "Hard Space".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

polkadot SSChampion Points: 13664 More actions · Answer 7

cha cha cha! You see I wouldn't have lasted to 160 if I'd started with char(0), char(1), char(2). I would have given up long before 160 thinking it was idleness.

Thanks so much.

---EDIT. That only took care of one of the white spaces! Any other common offenders?

--Quote me

polkadot SSChampion Points: 13664 More actions · Answer 8

ok it was char(13). OK, thanks so much to both of you. Two replaces resolves the leading white spaces.

--Quote me

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 9

polkadot (10/21/2013)
ok it was char(13). OK, thanks so much to both of you. Two replaces resolves the leading white spaces.

Ugh! 🙂 CHAR(13) is a carriage return. Sounds like they may have tried to force EOL returns instead of letting nature take it's course.

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)