CODEWITHSUNDEEP
mysqlsql
jonny
jonny

Reputation: 1

Selecting data of a certain value

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.

Upvotes: 0

Views: 124

Answers (6)

RMalke
RMalke

Reputation: 4094

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;

Upvotes: 0

John Hartsock
John Hartsock

Reputation: 86872

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]+$'

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85056

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]+$'

Upvotes: 4

Paul Sasik
Paul Sasik

Reputation: 81459

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.

Upvotes: 2

Oscar Gomez
Oscar Gomez

Reputation: 18488

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

Upvotes: 0

Bojangles
Bojangles

Reputation: 101483

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.

Upvotes: 0

Related Questions