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?
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
;
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
;
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?
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
;
Your are THE MAN. I need your tutorials geez. Thank you