SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


can't get rid of leading zeroes


can't get rid of leading zeroes

Author
Message
polkadot
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
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
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2250 Visits: 979
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
polkadot
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
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
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2250 Visits: 979
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
polkadot
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
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
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
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
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207909 Visits: 41966
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
polkadot
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
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
polkadot
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 1257
ok it was char(13). OK, thanks so much to both of you. Two replaces resolves the leading white spaces.

--Quote me
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207909 Visits: 41966
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search