Leading Zeros - 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

General SQL Server Forums

New to SQL Server Programming

Leading Zeros

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2010-02-26 : 02:24:46

Please assist,

I want to join two tables on the material nrs.The one has leading zeros (example "000000000060018515") and the other not (example:"60018515").
I need the 2nd table's material nrs to have the leading zeros, how do I update my table?

Thanks!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-26 : 02:26:41

What is the datatype of material nrs??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2010-02-26 : 02:31:00

The problem some has text in it so you cant cast the nr...example "0000000000S6001851"

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2010-02-26 : 02:46:18

Please assist...

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 02:55:09

Are the leading zeros of fixed length?

PBUH

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2010-02-26 : 02:59:53

No, because the length of the actual Material number differs but in total (pluss the zeros) comes to 18 length.

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2010-02-26 : 03:21:32

Please assist...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-26 : 03:21:39

update table
set nrs = right('000000000000000000'+nrs,18)

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 04:39:18

Try if it can help you

DECLARE @str AS varchar(100), @s AS varchar(100)

set @str = ''
SET @s = '000000000060018510'

while(left(@s,1) = '0')
Begin
set @s=right(@s,len(@s)-1)
SET @str = @s
End
SELECT @s

Vabhav T

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 04:53:54

quote:
Originally posted by vaibhavktiwari83

Try if it can help you

DECLARE @str AS varchar(100), @s AS varchar(100)

set @str = ''
SET @s = '000000000060018510'

while(left(@s,1) = '0')
Begin
set @s=right(@s,len(@s)-1)
SET @str = @s
End
SELECT @s

Vabhav T

But I think webfred's method is much more simple.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 05:34:19

But i dont think it will achieve the need.

Vabhav T

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 06:09:42

quote:
Originally posted by vaibhavktiwari83

But i dont think it will achieve the need.

Vabhav T

Why is that?
DECLARE @s AS varchar(100)
SET @s = '000000000060018510'
SELECT Right('000000000000000000'+@s,18)
SET @s = '18510'
SELECT Right('000000000000000000'+@s,18)

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 06:15:48

quote:
Originally posted by Idera

quote:
Originally posted by vaibhavktiwari83

But i dont think it will achieve the need.

Vabhav T

Why is that?
DECLARE @s AS varchar(100)
SET @s = '000000000060018510'
SELECT Right('000000000000000000'+@s,18)
SET @s = '18510'
SELECT Right('000000000000000000'+@s,18)

PBUH

With the abouve query desired result will not come...

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 06:19:21

I suppose requirement is like if string is '000000002340989384' then result should be '2340989384'
and if string is '00000000A340989384' then result should be 'A340989384'

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 06:20:58

Means Zeros should be removed from starting upto last occurrence before first occurrence of nonzero.

Vabhav T

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 06:52:27

quote:
Originally posted by Rheinhardt

I need the 2nd table's material nrs to have the leading zeros
, how do I update my table?

Thanks!

Please read the requirement of the OP marked in red.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 07:02:00

Okey i got it...
I misinterpret... sorry

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 02:08:47

quote:
Originally posted by vaibhavktiwari83

Try if it can help you

DECLARE @str AS varchar(100), @s AS varchar(100)

set @str = ''
SET @s = '000000000060018510'

while(left(@s,1) = '0')
Begin
set @s=right(@s,len(@s)-1)
SET @str = @s
End
SELECT @s

Vabhav T

Why are you using a while loop?
This can be easily done

DECLARE @str AS varchar(100), @s AS varchar(100)

set @str = ''
SET @s = '000000000060018510'
SELECT @s*1

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 02:14:27

or if you want the end result still in string

select convert(varchar(100), convert(int, @s))

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources