[dba-SQLServer] SQL Nightmares

It doesn't seem to like the Right( part of the function.  Right is not
correctly colored. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul 
> Nielsen
> Sent: Wednesday, March 19, 2008 11:08 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [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