SQL

[Jono]

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??) ? - MS SQL Server 2005.

 

[GravyMonster]

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.

 

[Pho]

Hardly the best solution in the world, and probably won't work but it's an idea ..

SELECT * FROM ADDRESS
WHERE STREET LIKE '%[0-999]%[a-Z]%' OR STREET LIKE '%[a-Z]%[0-999]%'

 

[Jono]

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)

 

[Jono]

Well it should work as I require anyway... i suspect the DB is gay.