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?
 
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you loading the data into the database?
0
 
sam2929Author Commented:
using ssis
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Please try this -

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

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

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

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

(1 row(s) affected)
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.