data conversion

hi,
i am getting data as 191121000000000 from excel which is loaded to database as 1.91121E+14 this column is nvarchar(100).

Is it possible to convert this data back to 191121000000000 in database i mean update that column from 1.91121E+14 to 191121000000000.


Thanks
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you loading the data into the database?
0
sam2929Author Commented:
using ssis
0
Pawan KumarDatabase ExpertCommented:
Please try this -

/*------------------------

SELECT LTRIM(STR(CAST(1.22222222222222E+52 AS FLOAT),150))

------------------------*/

------------------------------------------------------------------------------------------------------------------------------------------------------
12222222222222201000000000000000000000000000000000000

(1 row(s) affected)
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Pawan KumarDatabase ExpertCommented:
>>Is it possible to convert this data back to 191121000000000 in database i mean update that column from 1.91121E+14 to 191121000000000.
Yes it is possible

Please find the Easy and TESTED Solution for you -

/*------------------------

SELECT LTRIM(STR(CAST(1.91121E+14  AS FLOAT),150))
------------------------*/

------------------------------------------------------------------------------------------------------------------------------------------------------
191121000000000

(1 row(s) affected)

Open in new window

0
sam2929Author Commented:
that column has data like
1.91121E+14
M00203
ABCDE

i only want to convert first row not all rows
0
Pawan KumarDatabase ExpertCommented:
>>i only want to convert first row not all rows
Yes it is possible.

We can easily do like below -

CASE WHEN TRY_CAST(s AS FLOAT) IS NOT NULL THEN LTRIM(STR(CAST(s  AS FLOAT),150)) ELSE s END s

/*------------------------

SELECT CASE WHEN TRY_CAST(s AS FLOAT) IS NOT NULL THEN LTRIM(STR(CAST(s  AS FLOAT),150)) ELSE s END s
FROM 
(
	SELECT '1.91121E+14' s UNION ALL
	SELECT 'M00203' UNION ALL
	SELECT 'ABCDE' 
)k 
------------------------*/
s
------------------------------------------------------------------------------------------------------------------------------------------------------
191121000000000
M00203
ABCDE

(3 row(s) affected)

Open in new window

0
sam2929Author Commented:
there are many records so can't do union all is there way to fix in excel file then
0
Pawan KumarDatabase ExpertCommented:
Dump all the records in a table with NVARCHAR(MAX) and then using SQL query do the modification.
0
sam2929Author Commented:
yes i am dumping it as NVARCHAR(50) NULL  after that i can't do LTRIM(STR(CAST(partnumber AS FLOAT),150)) as data is different so getting
error Msg 8114, Level 16, State 5, Line 3
Error converting data type nvarchar to float.
0
Pawan KumarDatabase ExpertCommented:
Have you used below ? If not please use below-

SELECT 
      CASE WHEN TRY_CAST(yourcolumnName AS FLOAT) IS NOT NULL 
                    THEN LTRIM(STR(CAST(yourcolumnName  AS FLOAT),150)) 
     ELSE 
         yourcolumnName  
     END 
      yourcolumnName 
FROM  yourtableName

Open in new window

0
Pawan KumarDatabase ExpertCommented:
If your SQL is old you can use below -

SELECT CASE WHEN yourcolumnName LIKE '%[^0-9]%' AND yourcolumnName LIKE '%.%' AND yourcolumnName LIKE '%E+%'
		THEN LTRIM(STR(CAST(yourcolumnName AS FLOAT),150)) ELSE yourcolumnName END yourcolumnName
FROM yourtable

Open in new window


Sample trial

/*------------------------

SELECT CASE WHEN s LIKE '%[^0-9]%' AND s LIKE '%.%' AND s LIKE '%E+%'
		THEN LTRIM(STR(CAST(s  AS FLOAT),150)) ELSE s END s
FROM 
(
	SELECT '1.91121E+14' s UNION ALL
	SELECT 'M00203' UNION ALL
	SELECT 'ABCDE' UNION ALL
	SELECT '10'
)k 

------------------------*/
s
------------------------------------------------------------------------------------------------------------------------------------------------------
191121000000000
M00203
ABCDE
10

(4 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
is there way to fix in excel file then

Yes, usually.  In Excel, convert the column to TEXT format / "data type".


If data still won't load correctly, then do this;

Create a new sheet in the spreadsheet.

Copy all data into the new sheet from the original sheet.

Set the format on all columns on the new sheet to "TEXT".

Delete the old sheet.

Save the spreadsheet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.