CODEWITHSUNDEEP
sqlsql-server
Ivo
Ivo

Reputation: 3436

Get all records that contain a number

It is possible to write a query get all that record from a table where a certain field contains a numeric value?

something like "select street from tbladdress where street like '%0%' or street like '%1%' ect ect"

only then with one function?

Upvotes: 4

Views: 16437

Answers (5)

Susan Raj
Susan Raj

Reputation: 11

To fetch rows that contain only numbers,use this query 

select street 
from tbladdress 
where upper(street) = lower(street)

Works in oracle .

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881573

Searching for text within a column is horrendously inefficient and does not scale well (per-row functions, as a rule, all have this problem).

What you should be doing is trading disk space (which is cheap) for performance (which is never cheap) by creating a new column, hasNumerics for example, adding an index to it, then using an insert/update trigger to set it based on the data going into the real column.

This means the calculation is done only when the row is created or modified, not every single time you extract the data. Databases are almost always read far more often than they're written and using this solution allows you to amortize the cost of the calculation over many select statement executions.

Then, when you want your data, just use:

select * from mytable where hasNumerics = 1; -- or true or ...

and watch it leave a regular expression query or like '%...%' monstrosity in its dust.

Upvotes: 1

Ivo
Ivo

Reputation: 3436

I found this solution " select street from tbladresse with(nolock) where patindex('%[0-9]%',street) = 1"

it took me 2 mins to search 3 million on an unindexed field

Upvotes: 0

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Try this

declare @t table(street varchar(50))
insert into @t 
    select 'this address is 45/5, Some Road' union all
    select 'this address is only text'

select street from @t
where street like '%[0-9]%'

street

this address is 45/5, Some Road

Upvotes: 14

martin clayton
martin clayton

Reputation: 78135

Yes, but it will be inefficient, and probably slow, with a wildcard on the leading edge of the pattern

LIKE '%[0-9]%'

Upvotes: 6

Related Questions