How to find column with all same repeating character 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)) &lt;&gt; '' 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!
Actually there is REPLICATE() function:

WHERE &lt;column&gt; = 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 &lt;column&gt; 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 &lt;column&gt; = '' 
   OR &lt;column&gt; NOT LIKE '%[^0]%'