Remove Leading Zero - 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 2005 Forums

Transact-SQL (2005)

Remove Leading Zero

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-12-02 : 20:35:52

Gurus,
I am trying to Concatenate Col1 and Col2 and would like to replace leading zero in Col2 ('010') with a blank space(' '):

Col1 Col2
C 010

Desired Output:
Col
C 10

Thanks in advance.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-02 : 20:45:20

Try this:


' ' + REPLACE(LTRIM(REPLACE(Col2, '0', ' ')), ' ', '0')

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 20:59:50

That won't work for '019', and will only work if there is a 0 in the trailing position also.

(returns D019 instead of D 19,for example)

Are ALL of the values leading zeros?

If they are all numeric and all integer values (no decimals) you could do this
Select col1 +char(10)+ convert(varchar,convert(int,col2))

Poor planning on your part does not constitute an emergency on my part.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-02 : 21:16:59

quote:
Originally posted by dataguru1971

That won't work for '019', and will only work if there is a 0 in the trailing position also.

(returns D019 instead of D 19,for example)

Try this and let me know if it did not work.


DECLARE @SomeTable TABLE
(
   Col1 VARCHAR(10),
   Col2 VARCHAR(10)
);

INSERT INTO @SomeTable(Col1, Col2)
   VALUES('C', '010'),
         ('C', '019');

SELECT Col1, Col2, Col1 + ' ' + REPLACE(LTRIM(REPLACE(Col2, '0', ' ')), ' ', '0')
  FROM @SomeTable AS T;

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 02:44:40

declare @tab table
(
col1 varchar(10)
)

insert into @tab(col1)
select * from
(
values
('010')
,('009')
)t(col1)

select convert(varchar(10),cast(col1 as int)) from @tab

o/p
10
9

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 02:53:08

Better approach than my earlier post

declare @tab table
(
col1 varchar(10)
)

insert into @tab(col1)
select * from
(
values
('010')
,('009')
)t(col1)

select substring(col1,patindex('%[1-9]%',col1),len(col1))
from
@tab

Iam a slow walker but i never walk back

kellog1
Starting Member

35 Posts

Posted - 2010-12-03 : 10:49:04

It does not match the requirement. The leading zero should be replaced by white space (' '). By your solution trim the leading '0'.

quote:
Originally posted by dineshrajan_it

Better approach than my earlier post

declare @tab table
(
col1 varchar(10)
)

insert into @tab(col1)
select * from
(
values
('010')
,('009')
)t(col1)

select substring(col1,patindex('%[1-9]%',col1),len(col1))
from
@tab

Iam a slow walker but i never walk back

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:04:35

this allows for strings like ' 00000345320000 '.
If they need to be fixed length then it's a lot simpler.

select replace(left(col,patindex('%[1-9]%',col)-1),'0',' ') + right(col,len(col+'>')-1 - patindex('%[1-9]%',col)+1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources