Get all records that contain a number

Question

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?

Answer 1

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

LIKE '%[0-9]%'

Answer 2

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

Answer 3

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

Answer 4

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.