CODEWITHSUNDEEP
sqlsql-server
mj828
mj828

Reputation: 63

How do you select a record that has 4 digits/characters in a specific field?

I need to select all records where there are only 4 digits in a specific field.

I'm using SQL Server. The WHERE condition is looking at a character field that contains numbers. I want to select all the records where this field only has 4 numbers.

SELECT * FROM Server.database.schema.table
WHERE ci_dnis [has 4 characters]

Upvotes: 2

Views: 10221

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want four digits, then:

WHERE ci_dnis LIKE '[0-9][0-9][0-9][0-9]'

Or:

WHEN LEN(ci_dnis) = 4 AND           -- has a length of 4
     ci_dnis NOT LIKE '%[^0-9]%'    -- has no non-digits

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

If ci_dnis has integer data type you could use:

SELECT * FROM Server.database.schema.table WHERE ci_dnis BETWEEN 1000 AND 9999;

Otherwise:

SELECT * FROM Server.database.schema.table WHERE LEN(ci_dnis) = 4;

Upvotes: 2

Related Questions