[dba-SQLServer] SQL Nightmares

Hi John,

For the house number...
  select cast('000120' as int)


for the streetname...
  declare @col VARCHAR(25)
  set @col = '00000000012st'
  select right(@col,len(@col)-patindex ('%[^0]%', at col)+1)

-Paul 







-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 19, 2008 5:29 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Nightmares

I have another (different) database from hell.  This one has data fields
like 

HOUSENUMBER		StreetName		StreetUnit
00000006		000012th		Place

As you can see, if I do a simple append, then I get something like

00000006 000012th Place.

I can tell you that won't fly.  So I need to strip off leading zeros, pretty
much in ALL my fields, but at the very least in a fixed set of fields.  How
do I do this in SQL, bearing in mind that this table has 90 million records
and taking a week for one field is out of the question.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


__________ NOD32 2960 (20080319) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com