April 25, 2011 at 9:52 am
I have a field that can contain different types of info, I want to match a
10-digit number. How can I do this with the 'LIKE' statement (or any other statement).
something like
WHERE field LIKE '%1234567890%'
except that it has to match ANY 10 digit number
April 25, 2011 at 10:17 am
I think what you are looking for is
WHERE field NOT LIKE '%[^0-9]%'
The square brackets specify a subset of characters determined by the contained expression.
The caret (^) at the beginning of the expression produces the set that is the inverse of the remaining expression.
So essentially what this says is to give you all fields that do not contain any non-digit, i.e., all of the characters in the field are digits.
Drew Allen
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 25, 2011 at 2:14 pm
If you're looking for any row with a value that contains a character that is not 0-9 then drew.allen's solution will work but that seems slightly different than what you asked (I think). To specifically find a # containing exactly 10 digits you can use LIKE in this way:
DECLARE @my_table TABLE
(
info VARCHAR(100) PRIMARY KEY
) ;
INSERT INTO @my_table
(info)
VALUES ('1234567890'),
('abc');
SELECT *
FROM @my_table
WHERE info LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply