Scientific Format Coversion

Hello I have a column called [ID#] and values Are a Mixture
T19293010
T12003010
97160315C
9.05108058e+011

The ID# column is varhcar(510)

How can I convert the scientific numbers with e to be a standard number?

Something like this perhaps:

select case
          when try_cast([id#] as float) is null
          then [id#]
          else ltrim(str(cast([id#] as float),510,0))
       end
  from yourtable
;
1 Like

That worked perfect anyway to make just those numbers 11 digits?

select case
          when try_cast([id#] as float) is null
          then [id#]
          else right('00000000000'+ltrim(str(cast([id#] as float),510,0)),11)
       end
  from yourtable
;

I wonder why its converting this

9.42E+27

Into this

000000000000

select case
          when try_cast([id#] as float) is null
          then [id#]
		  when len(ltrim(str(cast([id#] as float),510,0)))>11
		  then ltrim(str(cast([id#] as float),510,0))
          else right('00000000000'+ltrim(str(cast([id#] as float),510,0)),11)
       end
  from yourtable
;
1 Like

Almost there Thanks for your help>

Last thing its adding leading Zeros on the numbers that are less than 11 digits:

Turned this
187662601

Into this
00187662601

Wasn't that what you asked for?

anyway to make just those numbers 11 digits?

1 Like

Yeah Ill alter those. Thanks again!

Awesome thank you. Now if I can get a query that will alter the ID# to 12 or less that would be the last part of what I need.

I forgot to mention that some numbers are up to 12 digits.

If their are more than 12 digits I want to trim it if there are less I'll take it just like it is. Don't need leading zeros.

Man I hope this helps. I really appreciate all your help with me!!!!!!!!!!!!!!!!!! Not in a rush today for it.

Exactly how would you like it trimmed?

Ex. 123456789123456
Should it be 123456789123 (left most 12 chars) or 456789123456 (right most 12 chars)?

The left most 12
123456789123

select case
          when try_cast([id#] as float) is null
          then [id#]
          when len(ltrim(str(cast([id#] as float),510,0)))>12
          then left(ltrim(str(cast([id#] as float),510,0)),12)
          else ltrim(str(cast([id#] as float),510,0))
       end
  from yourtable
;
1 Like

Your are THE MAN. I need your tutorials geez. Thank you