SQL

Man of Honour
Joined
18 Oct 2002
Posts
13,262
Location
Northallerton/Harrogate
Is it possible to select where a column could contain data like
12, 12A, Unit 12A, 12 Nubby Cottages etc?

What I want is to get only the rows with alpha-numeric data; I don't want just a number, or just a word - has to have both, but the way the data's in the table (or rather a view - I'm doing a query on a view, I believe) there's no structure to it - e.g. number then text... or text then a number.

Is this possible (and how??) :o? - MS SQL Server 2005.
 
SQL 2005 allows regular expressions to be used in queries - here

Should be fairly straightforward to write a regexp that just looks for a field with alpha and numeric value.
 
Hardly the best solution in the world, and probably won't work but it's an idea :p..

Code:
SELECT * FROM ADDRESS
WHERE STREET LIKE '%[0-999]%[a-Z]%' OR STREET LIKE '%[a-Z]%[0-999]%'
 
Cheers andy, sort of works... sorry for the delay...

How would I get it to cope with something with a - or / in it
Like 1-3 farm cottages...

It works with - / when they're not at the start...it's returning: e.g.
BUILDING 20/11 SHARNBROOK
CARDIGAN GARDENS 16-18 CARDIGAN STREET

Also, needs to deal with spaces now I come to think of it - how would I deal with
1 - 3 Farm Cottages

Anyone? (without it getting silly, if at all possible)
 
Last edited:
Well it should work as I require anyway... i suspect the DB is gay.
 
Back
Top Bottom