Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why the [^0] below doesn't match all zeros.

CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) = '' THEN 1 ELSE 0 END IsEmpty
CASE WHEN LTRIM(RTRIM(IL.ItemNumber)) <> '' AND LTRIM(RTRIM(IL.ItemNumber)) NOT LIKE '[^0]' THEN 1 ELSE 0 END IsZeros

I'm basically trying to validate the column. Trimming works for empty, but maybe there is a much cleaner/flexible way to accomplish both of these validations? Thanks!

share|improve this question
    
[^0] matches only a single character, and this is because of the definition of the patterns used with LIKE. –  dezso Sep 9 '13 at 13:53
add comment

1 Answer

up vote 3 down vote accepted

Actually there is REPLICATE() function:

WHERE <column> = REPLICATE('0',15)

Funny thing, it's actually using 17 characters, exactly the same as the '000000000000000' literal.

(Reading again the question, I suppose you don't want the above either because it has the 15 hard coded.)


The LIKE condition you have is not working as expected because the LIKE '[^0]' would be true only if the column had exactly 1 character. I think you need to check if it has only 0s or not:

WHERE <column> NOT LIKE '%[^0]%'

To check if it has only 0s or only spaces, this do (for CHAR and VARCHAR columns) and you don't even have to trim the column:

WHERE <column> = '' 
   OR <column> NOT LIKE '%[^0]%'
share|improve this answer
    
Right, maybe I should of expanded in my question. This is raw data. So before this process, I'm padding/zero-filling (using replicate) the value if it is not empty. –  JIsaak Sep 9 '13 at 13:22
    
Nice, that seems to work. I wish there was an elegant way to check for all zero and empty in the same LIKE clause, but reading through the msdn it doesn't look possible. Thanks for your help! –  JIsaak Sep 9 '13 at 13:52
    
Nevermind, I think %[^0 ]% will work for empty as well. Seriously, thanks! –  JIsaak Sep 9 '13 at 13:57
    
Yes, but it will also allow strings like '00 000 00 ' –  ypercube Sep 9 '13 at 14:06
    
Good point. For my scenario, that is acceptable. –  JIsaak Sep 9 '13 at 14:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.