CODEWITHSUNDEEP
sqlsql-server
Suresh
Suresh

Reputation: 1169

Removing Non-Numeric Characters from String Fail in a Scenario + MS SQL Server

I am writing a migration script from MS SQL server 2000 to MS SQL Server 2012, I would like to migrate varchar field data to float, some of values in old data having non numeric data which I removed using below code, and it works in most of case, except below values where conversion fail, logically .(DOT) is valid value but due to multiple .DOT its fail.

I am thinking to ignore values where conversion fail, but haven't found to ignore it.

Column Values

1.2.4

5..4

..7.5.3

SELECT Left(SubString(field, PatIndex('%[0-9.-]%', field), 8000), 
                        PatIndex('%[^0-9.-]%', SubString(field, PatIndex('%[0-9.-]%', field), 8000) + 'X')-1)

After Suppress below is final code, which worked fine

CASE WHEN (len(field) - len(replace(field, '.', '')) <= 1 AND field!='.' ) THEN
CAST(ISNULL(REPLACE(REPLACE(LEFT(SubString(field, PatIndex('%[0-9.-]%', field), 8000), 
                PatIndex('%[^0-9.-]%', SubString(field, PatIndex('%[0-9.-]%', field), 8000) + 'X')-1) ,'-',''),'..','.'),'')
                as float)
END

Upvotes: 1

Views: 421

Answers (1)

Alex K.
Alex K.

Reputation: 175748

Count the dots, suppress (NULL) if > 1 

select case 
   when len(field) - len(replace(field, '.', '')) > 1 then null 
   else Left(..) end

Or in a where clause:

where len(field) - len(replace(field, '.', '')) <= 1

Upvotes: 1

Related Questions