CODEWITHSUNDEEP
sqlsql-server-2008string
Kuzgun
Kuzgun

Reputation: 4737

How to detect empty fields?

I have a column (nvarchar) which has some nonsense data and I'm trying to make a clean up. What I want to do is to delete all blank fields and have only fields with texts or numbers. What I tried so far:

DELETE FROM myTable WHERE col='' OR col=' ' OR LEN(col)=0 OR LTRIM(RTRIM(col))=''

However, there are still some blank fields. Some of them are like these (single quotes and commas are not included, I write them to show where they start and end)

'     ' , '               ', '

', '

'

Upvotes: 1

Views: 88

Answers (2)

wala rawashdeh
wala rawashdeh

Reputation: 433

you can replace the extra signs with blank and then trim from left and right if they are = '' then delete like this :

DELETE FROM myTable 
WHERE LEN(col)=0 
        OR 
       LTRIM(
        RTRIM(
                REPLACE(REPLACE(REPLACE(REPLACE(col,'.',''),'''',''),'"',''),',','')
            )
        )=''

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

this will do exactly the same:

DELETE 
FROM myTable 
WHERE col=''

If you still have rows, it is because you have other characters that does not appear like linefeed

This will delete all rows that doesn't have alphanumeric characters in the text

DELETE 
FROM myTable 
WHERE col not like '%[0-9a-zA-Z]%'

You can add more characters. Example:

[0-9a-zA-Z,'']

<--- added quote and comma to rows that shouldn't be deleted

Upvotes: 2

Related Questions