How to pull only scientific notation values from table

Transact-SQL
1

I have invalid values of scientific notation in my table. How can I pull only those so that I can later update the column?

The column (Fedex_ID) attributes are varchar(23) NULL.

Example of invalid data: 7.95059E+11 or 8.05242E+11

2

please post your table definition (CREATE TABLE statement), samples of the invalid data

3

Why are they invalid? Are all values with scientific notation invalid? Do you want them converted to numeric? Are these imported from Excel?

4

I would extract the scientific notation values with like operator..

Declare @Fedx table
(FedxID varchar(23))

insert into @Fedx
select '7.95059E+11'
UNION
select '8.05242E+11'

select FedxID from @Fedx where FedxID like '%E+%'

5

OK -- but what do you mean by "later update the column"

6

Don't think this will work since I don't know up front what the exact scientific notation values will be...All I know is that there are some in the table.

7

As to questions:
(a) Why are they invalid? -- because they are not valid fedex tracking #s.
(b) Are all values with scientific notation invalid? -- yes
(c) Do you want them converted to numeric? -- no ...even when converted, they appear to be invalid
(d) Are these imported from Excel? -- they are imported via SSIS into a SQL table via a csv file

8

The problem happens when the CSV is created. Probably it is created by Excel and the column is General format in Excel. Change it to Text before exporting to csv

9

My question still is: How can I pull only those that have the scientific notation from my table. There are approx 10k records to review. Using WHERE FEDEX_ID LIKE '%E+%' does not work.

Thanks!

10

What I meant by "later update the column".... I need to first identify those having the invalid scientific notation rows, then perform an UPDATE with the correct fedex_id tracking number once I obtain those.

11

Might this do any good?

WHERE Fedex_ID LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9]E+[0-9]%'

or if the numeric bit is variable length then

WHERE Fedex_ID LIKE '[0-9].[0-9]%[0-9]E+[0-9]%'
12

Thank you for your suggestion... However, I have approx 6k rows with scientific notation and it only picks up 190 using your statement.

It is not picking up some of them such as:

6.11E+11

Even when I use: Fedex_id like '6.%' it doesn't pick up the above value.

13

Leading space perhaps?

Would it perhaps be enough to check for

Fedex_id like '%E%'
14

I've tried different variations... I'm stumped!

--and fedex_id like '%E%' -- not picking up all of them
--and fedex_id like '%.%' -- not picking up all of them
--and fedex_id like '%+%' -- not picking up all of them
--and Fedex_id like '%E+%' -- not picking up all of them

15

If it were me, I'd talk to whomever is supplying the CSV and get them to fix it

1 Like
16

Yes i agree with @gbritton and check with provider of CSV file data to fix..

But another option i will look for the records with not isnumeric records or
patindex('%[~`!@#$%^&*()_=+|{};",<>/?a-z]%',fedex_id)