Selecting data of a certain value

Question

I have a table called customers, with a field called telnumber.

The telnumber contains a telephone number, but does have some invalid data. So I want to select data from the customer table, field telnumber where the value is between 9 and 11 in length and is numeric.

Answer 1

This should return only records that have length between 9 and 11 AND are numeric.

SELECT * FROM customers
WHERE LENGTH(telnumber) BETWEEN 9 AND 11
AND telnumber REGEXP '^[0-9]+$'

Answer 2

Your requirement of 9 and 11 in length and is numeric means that you automatically reject any common "phone number punctuation" such as dashes, spaces and parentheses. Your telnumber field may actually contain properly "punctuated" telephone numbers. I think a more complete solution would use MySQL's RegEx to test for valid phone numbers.

Here's another SO question/answer to get you started and perhaps thinking of alternatives.

Answer 3

SELECT
  * 
FROM customers c
WHERE LENGTH(c.telnumber) BETWEEN 9 AND 11
  AND c.telnumber REGEXP '^[0-9]+$'

OR

SELECT
   *
FROM customers c
WHERE LENGTH(c.telnumber) >=9 AND LENGTH(c.telnumber) <= 11
   AND c.telnumber REGEXP '^[0-9]+$'

Answer 4

If we take a look here and use LENGTH(), a simple query would look something like this:

SELECT * FROM customers WHERE (LENGTH(telnumber) > 9) AND (LENGTH(telnumber) < 11)

This can be expanded to suit your needs.

Answer 5

You can use this stored procedure to determine if it is a numeric data type:

CREATE FUNCTION dbo.IsNumericEx( @value nvarchar(max) )
RETURNS BIT
AS
BEGIN
     DECLARE @isInt BIT

     SELECT  @isInt = 1 
     WHERE   @value NOT LIKE '%[^0-9.-]%'
             AND LEN(REPLACE(@value, '.', '')) >= LEN(@value) - 1
             AND CHARINDEX('-', @value) IN (0, 1)
             AND CHARINDEX('-', @value, 2) = 0

     RETURN ISNULL(@isInt, 0)
END
GO

And the query:

select * from myTable where isNumericEx(mycolumn) = 1 and length(mycolumn) between 9 and 11

Answer 6

I'm not an expert in mySql, but I think you can simply use:

SELECT *
FROM customers
WHERE CONVERT(telnumber, INTEGER) IS NOT NULL
AND LENGTH(telnumber) >= 9
AND LENGTH(telnumber) <= 11;