Selecting numbers to the right of the leading zeros

[arodri]

I have a table that I need to select the correct location numbers from but I can't seem to select the right numbers that I need.
The location number is always the last number after all of the zeros to the right of it.

For example, if in my table it reads "0000000004" then the location number I need to select is 4.

I tried just using a replace statement to reaplce any zeros with a blank but then I run into problems when the location in the table reads "000000000606" because I need to select 606 as the location.

I also tried just selecting the last three numbers but in the first example that would give me a location of 004 and I only need it to be 4.


Is there a way to select all of the numbers to the right of the leading zeros?

Thanks in advance!

 

[Olaf Doschke]

Convert to numeric. CONVERT() helps

SELECT Convert(yourtable.fiels, float) As number From Yourtable

Bye, Olaf.

 

[gmmastros]

There is a handy little trick you can use....

Replace 0 with <space>, do an LTrim, and then replace <space> with 0.

Ex:

Declare @Temp Table(Data VarChar(30))

Insert Into @Temp Values('000000000400')
Insert Into @Temp Values('000000000606')
Insert Into @Temp Values('000000ABC012')

Select Replace(LTrim(Replace(Data, '0', ' ')), ' ', '0')
From   @Temp

Note that this works for alpha-numeric strings, too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

Maybe I'm missing something but how would converting my field help in selecting all numbers to the right of the leading zeros?

 

[arodri]

Oh sorry! the query wasn't working at first but got it now. Works perfectly...thank you so much!

 

[Olaf Doschke]

ok, still to answer: converting '0040' to numeric results in 40, as would do for '40', '040' or even ' 040', that's the nature of conversion, leading whitespace or zeros don't matter.

You can also use gammastros nice trick, if you want or need to stay with the type of strings.

Bye, Olaf.