LTRIM() Modified - 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)

LTRIM() Modified

Author

Topic

wali
Starting Member

7 Posts

Posted - 2007-02-09 : 14:17:47

hello everybody
I have a table with one colum in which i need to get rid of the leading 0's. It would have been easier to just change the datatype to int. However i can not since some columns have some characters in them.

LTRIM() only seems to get rid of leading blanks..Anybody know a function on how to get rid of Leading zeros.
i.e

00089 gets changed to 89.

Thanks

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 14:30:58

select substring(TargetField, 2, len(TargetField))
from Table1

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-02-09 : 14:38:24

write a function which will do something like:

declare @MyString varchar(100)
set @MyString = '000123'
while Left(@MyString,1)='0'
begin
set @Mystring = substring(@MyString,2,len(@MyString))
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 17:19:29

SELECT REPLACE(LTRIM(REPLACE(YourColumnNameHere, '0', ' ')), ' ', '0')
FROM YourTableNameHere

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 18:07:43

[code]
select substring(ltrim(x),patindex('%[^0]%',ltrim(x)),datalength(ltrim(x)))
from
(
select x= '0009045' union all
select 'x 45' union all
select '23 45' union all
select ' 000045' union all
select ' 000045' union all
select ' 000.045' union all
select ' 45' union all
select ' 45' union all
select '00045'
) a

[/code]
Results:
[code]
--------
9045
x 45
23 45
45
45
.045
45
45
45
[/code]

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-09 : 18:11:03

quote:
Originally posted by Peso

SELECT REPLACE(LTRIM(REPLACE(YourColumnNameHere, '0', ' ')), ' ', '0')
FROM YourTableNameHere

Peter Larsson
Helsingborg, Sweden

This may be a problem:


SELECT Results = REPLACE(LTRIM(REPLACE(x, '0', ' ')), ' ', '0')
from
	(
	select x= '0009 45' union all
	select 'x 45' union all
	select '23 45'
	) a

Results
--------------
9045
x045
23045

(3 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 18:22:57

Yes, I noticed that too, but I read original post again and decided my suggestion was enough.

quote:
Originally posted by wali

I have a table with one colum in which i need to get rid of the leading 0's. It would have been easier to just change the datatype to int. However i can not since some columns have some characters in them.

He doesn't tell if the characters are spaces. All other characters are fine, but jut not spaces.

It the characters are spaces, just replace the space replacing with a character not possible to enter, BELL (char 7)

REPLACE(LTRIM(REPLACE(x, '0', char(7))), char(7), '0')

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources