SQLTeam.com | Weblogs | Forums

Scientific Format Coversion


#1

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?


#2

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
;

#3

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


#4
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
;

#5

I wonder why its converting this

9.42E+27

Into this

000000000000


#6
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
;

#7

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


#8

Wasn't that what you asked for?

anyway to make just those numbers 11 digits?


#9

Yeah Ill alter those. Thanks again!


#10

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.


#11

Exactly how would you like it trimmed?

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


#12

The left most 12
123456789123


#13
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
;

#14

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