Ignoring Numbers in a field 1

[kenhutchings]

Hi all,

Our company db is in need of a bit of an overhaul and some incredibly useful person has suggested that it would be good if when an address line is ordered that it ignores the numbers at the start eg

21 Bill Street
21 Ben Street

The numbers would be ignored and then ordered by the street

Can this be done

All help much appreciated

 

[vongrunt]

Possible, but somewhat tricky and slow on larger tables.

It would be better to have number and street as two separated columns.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kenhutchings]

I thought as much the problem is that our main db has data impored to it from 2 other apps - both from 3 parties and not operated by myself - so convincing some of the fools here that 'its for the best' would be easier said than done

Thanks for the info

Ken

 

[SQLDenis]

And it they tell you keep the design you can always code a mess like this
order by replace(replace(replace(replace(field,'1',''),'2',''),'3',''),'4','') of course keep going till 10
you could also add left 5 in fron of the field

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

and a ltrim of course

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/