November 15, 2015 at 1:30 pm
Often when visiting forums I'll see someone recommending a solution that includes a NOT IN statement ie.
SELECT *
FROM yourTable
WHERE yourColumn NOT LIKE '%[^0-9]%'
to solve the problem of finding rows having a column containing only numbers the above syntax is basically saying where youColumn is not like a column not having numbers.
I usually wonder why LIKE isn't used instead
ie.
where yourColumn has numbers eg. LIKE '%[0-9]%'
Is phrasing it in the double negative better for some reason?
Thanks.
--Quote me
November 15, 2015 at 1:37 pm
polkadot (11/15/2015)
I usually wonder why LIKE isn't used insteadie.
where yourColumn has numbers eg. LIKE '%[0-9]%'
Probably because they do two different things
NOT LIKE '%[^0-9]%'
Return the rows where the column consists of numeric characters (0-9) only
WHERE SomeCol LIKE '%[0-9]%'
Return the rows where the column contains 1 or more numeric characters as well as anything else
Pretty trivial to test:
CREATE TABLE #Test (
SomeCol VARCHAR(50)
);
INSERT INTO #Test
(SomeCol)
VALUES ('12345678'), ('abgey7233'), ('djskdjas')
SELECT *
FROM #Test
WHERE SomeCol NOT LIKE '%[^0-9]%'
SELECT *
FROM #Test
WHERE SomeCol LIKE '%[0-9]%'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2015 at 1:57 pm
Aha, I didn't consider the mixed letters/numbers variations to test with.
Great. Thanks GilaMonster.
--Quote me
November 18, 2015 at 7:41 am
May I also point out that NOT IN vs IN is a different matter than NOT LIKE and LIKE?
NOT IN and IN only consider whole value comparisons where NOT LIKE and LIKE consider partial comparisons to the values.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply