SQL how to find non-empty string?-CodePudding

I have a column of phone numbers, which may contain some empty strings. There is no NULL value.

SELECT COUNT(CONTACTPHONE2) FROM Auct_ABSENTEEBID
-- ANS 7844
SELECT COUNT(CONTACTPHONE2) FROM Auct_ABSENTEEBID WHERE CONTACTPHONE2 IS NOT NULL
-- ANS 7844

SELECT CONTACTPHONE2 FROM Auct_ABSENTEEBID
(908) 303-****


310-871-****

We can see there are empty strings between the phone numbers.

SELECT CONTACTPHONE2 FROM Auct_ABSENTEEBID WHERE CONTACTPHONE2 LIKE '%^(?!\s*$).*%'
SELECT COUNT(CONTACTPHONE2) FROM Auct_ABSENTEEBID WHERE CONTACTPHONE2 LIKE '%^(?!\s*$).*%'
--ANS 0

I used the regex ^(?!\s*$). ", but it return nothing and COUNT returns 0.

I don't know what cause the issue.

CodePudding user response：

Why use a regex at all? Just do a comparison to the empty string '', like:

WHERE NOT CONTACTPHONE2 = ''

That would undoubtedly perform better and it certainly is a bit simpler and more likely to 'just work'.