CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Andres Urrego Angel
Andres Urrego Angel

Reputation: 1932

SQL: How to make a replace on the field ''

I have a very but tricky question for you guys. So, listen I have a field with spaces and numbers in one of my table columns. The key part is transform the content in a decimal field. The drawback is basically that for some rows I could get something like:

    ' 1584.00 '
    ' 156546'
    '545.00 '
    '    '

So, to clean up my column, I have done a LTRIM and RTRIM so spaces gone. So now for a couple of records where the record were just spaces the new content is ''. Finally I need to convert this result to a decimal.

Issue: The thing is that for field that contend just the spaces the new result is '' and I'm not able to apply a REPLACE on this because it's a blank and the code below doesn't work:

SELECT REPLACE('','','0')

-- Final current verison
SELECT CAST(COALESCE(REPLACE(REPLACE([Gross_Weight],' ','0'),',',''),'0') AS DECIMAL(13,3))

How could I figure it out?

thanks so much

Upvotes: 1

Views: 52

Answers (3)

Shammas
Shammas

Reputation: 461

I presume your data is in a table. Lets call this table 'DATA' and the column 'VALUE' Then you might use the below query

UPDATE DATA SET VALUE = 0 where VALUE = ''

To select the value do the below

select case  ltrim(rtrim([Gross_Weight])) when ''
   THEN 0
   ELSE ltrim(rtrim([Gross_Weight])) END

Let me know if i get the requirement wrong.

Upvotes: 0

itsLex
itsLex

Reputation: 786

No need for replace, just concatenate a zero to your column, like

SELECT RTRIM('0' + LTRIM(column))

Upvotes: 1

Tom H
Tom H

Reputation: 47444

SELECT COALESCE(NULLIF(MyColumn, ''), 0)

This has the side-effect that you will also turn NULL values into 0, which you might not want. If that's a problem then a simple CASE statement should do the trick:

SELECT CASE WHEN MyColumn = '' THEN 0 ELSE CAST(MyColumn AS DECIMAL(10, 4)) END

Obviously you'll also have to incorporate any other manipulations that you're already doing.

Upvotes: 1

Related Questions