Sql-server – How to find column with all same repeating character

sql-server-2008-r2t-sql

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!

Best Answer

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]%'
Related Question