Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can't get rid of leading zeroes Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 3:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
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?
Post #1506891
Posted Monday, October 21, 2013 3:39 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 588, Visits: 900
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
Post #1506893
Posted Monday, October 21, 2013 3:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
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?
Post #1506894
Posted Monday, October 21, 2013 3:56 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 588, Visits: 900
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
Post #1506895
Posted Monday, October 21, 2013 4:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
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?
Post #1506903
Posted Monday, October 21, 2013 4:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
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

Post #1506909
Posted Monday, October 21, 2013 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506911
Posted Monday, October 21, 2013 4:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
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?
Post #1506912
Posted Monday, October 21, 2013 4:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 297, Visits: 797
ok it was char(13). OK, thanks so much to both of you. Two replaces resolves the leading white spaces.
Post #1506918
Posted Tuesday, October 22, 2013 3:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1507391
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse