"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:iqtcc2t2l3qs6u86uulm2853v187ir73lp@4ax.com...
> On Tue, 25 Jul 2006 14:47:38 -0400, Roy Harvey wrote:
>
> (snip)
>>>SELECT REPLACE(LTRIM(REPLACE(x, '0', ' ')), ' ', '0')
>>>FROM Whatever;
>>
>>Very neat!
>
> Hi Roy,
>
> Agreed. I guess I should have credited the person I stole this from, but
> I don't remember. I pick up so many good tricks in these groups that
> it's hard to keep track of who posted what first. <g>
>
>>>SELECT RIGHT(x, 11 - PATINDEX('%[^0]%', x))
>>>FROM Whatever;
>>
>>Also nice, but I like the REPLACE better.
>
> It is somehow more elegant - but it's habit of messing up embedded
> spaces is annoying. For correctness, you need the RIGHT / PATINDEX
> version.
>
>>I figured there would be a few elegant solutions like these, but none
>>of them came to mind right away. It seemed to be a pretty good bet
>>nobody else where would write anything so brutal so it would at least
>>have the advantage of being different. 8-)
>
> Heh! So true!
>
> --
> Hugo Kornelis, SQL Server MVP

"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:73icc25bmgpt0356gdq4uda687uktvjbvs@4ax.com...
> There is always Brute Force. 8-)
>
> CREATE TABLE Whatever (x char(10))
> INSERT Whatever values ('0123456789')
> INSERT Whatever values ('0012345678')
> INSERT Whatever values ('0001234567')
> INSERT Whatever values ('0000123456')
> INSERT Whatever values ('0000012345')
> INSERT Whatever values ('0000001234')
> INSERT Whatever values ('0000000123')
>
> SELECT CASE WHEN SUBSTRING(x, 1,10) = REPLICATE('0',10)
> THEN ''
> WHEN SUBSTRING(x, 1, 9) = REPLICATE('0', 9)
> THEN SUBSTRING(x,10, 1)
> WHEN SUBSTRING(x, 1, 8) = REPLICATE('0', 8)
> THEN SUBSTRING(x, 9, 2)
> WHEN SUBSTRING(x, 1, 7) = REPLICATE('0', 7)
> THEN SUBSTRING(x, 8, 3)
> WHEN SUBSTRING(x, 1, 6) = REPLICATE('0', 6)
> THEN SUBSTRING(x, 7, 4)
> WHEN SUBSTRING(x, 1, 5) = REPLICATE('0', 5)
> THEN SUBSTRING(x, 6, 5)
> WHEN SUBSTRING(x, 1, 4) = REPLICATE('0', 4)
> THEN SUBSTRING(x, 5, 6)
> WHEN SUBSTRING(x, 1, 3) = REPLICATE('0', 3)
> THEN SUBSTRING(x, 4, 7)
> WHEN SUBSTRING(x, 1, 2) = REPLICATE('0', 2)
> THEN SUBSTRING(x, 3, 8)
> WHEN SUBSTRING(x, 1, 1) = REPLICATE('0', 1)
> THEN SUBSTRING(x, 2, 9)
> ELSE x
> END as NoLeadingZeroes
> FROM Whatever
>
> NoLeadingZeroes
> ---------------
> 123456789
> 12345678
> 1234567
> 123456
> 12345
> 1234
> 123
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 25 Jul 2006 12:07:46 -0400, "Orlando Acevedo"
> <orlando_acevedo@hotmail.com> wrote:
>
>>I am trying to use several tables that have one 10-character text field in
>>common.
>>Most of the records have a numeric expression, but some tables have
>>leading
>>0's, and some don't.
>>I can't cast the field to numbers because there are some records that have
>>letters also.
>>What function can I use to get rid of all the 0s at the left of each
>>record?
>>
>>Thanks!
>>

<hugo@perFact.REMOVETHIS.info.INVALID> wrote:

>If brute force is not your thing, then there's also the possibility of
>clever use of string functions.
>
>The query below works, unless you have data with embedded spaces:
>
>SELECT REPLACE(LTRIM(REPLACE(x, '0', ' ')), ' ', '0')
>FROM Whatever;

>And then there's the possibility of combining RIGHT and PATINDEX, to get
>the query below. I only got it to fail on '0000000000'.
>
>SELECT RIGHT(x, 11 - PATINDEX('%[^0]%', x))
>FROM Whatever;

"Arnie Rowland" <arnie@1568.com> wrote in message
news:upe0q5BsGHA.1296@TK2MSFTNGP02.phx.gbl...
> Hugo,
>
> The replace() is excellent!
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
> news:49occ2hmue9ghilalvbenlonrnto0ebkam@4ax.com...
>> On Tue, 25 Jul 2006 12:07:46 -0400, Orlando Acevedo wrote:
>>
>>>I am trying to use several tables that have one 10-character text field
>>>in
>>>common.
>>>Most of the records have a numeric expression, but some tables have
>>>leading
>>>0's, and some don't.
>>>I can't cast the field to numbers because there are some records that
>>>have
>>>letters also.
>>>What function can I use to get rid of all the 0s at the left of each
>>>record?
>>>
>>>Thanks!
>>>
>>
>> Hi Orlando,
>>
>> If brute force is not your thing, then there's also the possibility of
>> clever use of string functions.
>>
>> The query below works, unless you have data with embedded spaces:
>>
>> SELECT REPLACE(LTRIM(REPLACE(x, '0', ' ')), ' ', '0')
>> FROM Whatever;
>>
>> And then there's the possibility of combining RIGHT and PATINDEX, to get
>> the query below. I only got it to fail on '0000000000'.
>>
>> SELECT RIGHT(x, 11 - PATINDEX('%[^0]%', x))
>> FROM Whatever;
>>
>> --
>> Hugo Kornelis, SQL Server MVP
>
>

On Tue, 25 Jul 2006 12:07:46 -0400, Orlando Acevedo wrote:

>I am trying to use several tables that have one 10-character text field in
>common.
>Most of the records have a numeric expression, but some tables have leading
>0's, and some don't.
>I can't cast the field to numbers because there are some records that have
>letters also.
>What function can I use to get rid of all the 0s at the left of each record?
>
>Thanks!
>

On Tue, 25 Jul 2006 14:47:38 -0400, Roy Harvey wrote:

>>SELECT REPLACE(LTRIM(REPLACE(x, '0', ' ')), ' ', '0')
>>FROM Whatever;
>
>Very neat!

>>SELECT RIGHT(x, 11 - PATINDEX('%[^0]%', x))
>>FROM Whatever;
>
>Also nice, but I like the REPLACE better.

>I figured there would be a few elegant solutions like these, but none
>of them came to mind right away. It seemed to be a pretty good bet
>nobody else where would write anything so brutal so it would at least
>have the advantage of being different. 8-)

On Tue, 25 Jul 2006 13:31:35 -0700, Arnie Rowland wrote:

>I would think that if you were concerned about the embedded spaces, you
>could first replace() the embedded spaces with a placeholder, and then back,
>e.g.,
>
> replace( replace( ltrim( replace( replace( x, ' ', '~' ), '0', ' ' )), '
>', '0' ),'~',' ' )