Click here to monitor SSC
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
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
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?
Keith Tate
Keith Tate
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 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
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
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?
Keith Tate
Keith Tate
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 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
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
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?
polkadot
polkadot
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45137 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
polkadot
polkadot
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
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?
polkadot
polkadot
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1045
ok it was char(13). OK, thanks so much to both of you. Two replaces resolves the leading white spaces.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45137 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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