CODEWITHSUNDEEP
sql-server
bluerubez
bluerubez

Reputation: 300

Can not select rows where column values are empty string in SQL Server?

I have a column which has varchars like "172.54". I am trying to insert into another table where this columns datatype is float. I am getting error saying can not convert datatype varchar to float. So I do 

SELECT * 
FROM TBL 
WHERE ISNUMERIC(COLNAME) <> 1

And I get no results. But casting is not working. So I look and I have empty strings in that column. So I try to 

SELECT * 
FROM TBL 
WHERE COLNAME = ''

And also every other different amount of spaces.

I ultimately just want to convert the empty strings to null

Also len(colname) = 1

Upvotes: 0

Views: 761

Answers (2)

onupdatecascade
onupdatecascade

Reputation: 3366

declare @test varchar(10) = ' ' -- any number of spaces is equivalent to ''
select try_convert( float, @test ) as floatval -- '' gives you 0
select case when @test = '' then NULL else try_convert( float, @test ) end as floatval -- value '' returns NULL instead of 0

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

I guess you column has some characters other than numeric data. Also empty string will be converted to zero it will not throw error.

To filter Numeric data use 

COLNAME not like '%[^0-9]%'

Try something like this

insert into tablename (col1,col2)
SELECT col1,col2 FROM TBL
COLNAME not like '%[^0-9]%'

Upvotes: 0

Related Questions