Eliminating Leading Char Zeros

Question

Eliminating Leading Char Zeros

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Oops! Did the work last night, posted this morning, sorry. You have a knack for posting the perfect solution Peter, magnificent. The most concise and the fastest method, and exactly according to the spec.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

OlegS SSC Veteran Points: 213 More actions · Answer 2

Also without isnumeric() function

declare @Temp table (

Data nvarchar(14)

)

insert into @Temp

select '00000000000002'

insert into @Temp

select '00000000000100'

insert into @Temp

select '00000000002100'

insert into @Temp

select '000000000A4504'

select

replace(ltrim(replace(data,'0',' ')),' ','0')

from @Temp

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Heh... even better... nicely done!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Heh... I jumped the gun a bit... Oleg's code seems to do the trick nicely, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OlegS SSC Veteran Points: 213 More actions · Answer 5

I am not sure

declare @Temp table (

Data nvarchar(14)

)

insert into @Temp

select '00000000000000'

insert into @Temp

select '00000000000002'

insert into @Temp

select '00000000000100'

insert into @Temp

select '00000000002100'

insert into @Temp

select '000000000A4504'

select

replace(ltrim(replace(data,'0',' ')),' ','0')

from @Temp

where data != '00000000000000'

union all

select

data

from @Temp

where data = '00000000000000'

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

data OlegS Peso

-------------- -------------- --------------

00 00

00000000000000 0000000000000

00000000000002 2 2

00000000000100 100 100

00000000002100 2100 2100

000000000A4504 A4504 A4504

N 56°04'39.16"
E 12°55'05.25"

OlegS SSC Veteran Points: 213 More actions · Answer 7

declare @Temp table (

Data nvarchar(14)

)

insert into @Temp

select '00000000000000'

insert into @Temp

select '000000'

insert into @Temp

select '00'

insert into @Temp

select '00000000000002'

insert into @Temp

select '00000000000100'

insert into @Temp

select '00000000002100'

insert into @Temp

select '000000000A4504'

select

case

when len(replace(data,'0',' ')) = 0 then data

else replace(ltrim(replace(data,'0',' ')),' ','0')

end

from @Temp

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Oleg, I do not know which is right. I just posted the both approaches for comparison.

The only one qualified to determine the right approach is original poster.

N 56°04'39.16"
E 12°55'05.25"

cdun2 SSCertifiable Points: 7893 More actions · Answer 9

cdun2

SSCertifiable

Points: 7893

July 19, 2007 at 7:30 am

#720753

Thanks again!

CSDunn

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

You're welcome, but we sure would like to know what you want done if the entry contains all zeros

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65143 More actions · Answer 11

Notwithstanding the answer to the 'all zero' question

if emptry string is required then

LTRIM(SUBSTRING([column], PATINDEX('%[^0]%', [column]+' '), LEN([column])))

Far away is close at hand in the images of elsewhere.
Anon.

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 12

In light of the unanswered "edge" conditions how about this?

Use TempDB

If Object_Id('dbo.test') is not Null Drop table dbo.Test

Select '123'[CharNum] into dbo.Test

Union All Select '0123'

Union All Select '000123'

Union All Select '000'

Union All Select 'XYZ'

Union All Select ''

Union All Select '0'

Select IsNull(Stuff(CharNum,1,i-1,''),'0')[NormalizedCharNum]

from (Select CharNum,PatIndex('%[1-9a-z]%',CharNum) from dbo.Test) t

(Adjust the PatIndex pattern for your requirements and collation)

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Guess I don't understand... Oleg's code seemed to work fine for all conditions according to what the OP wanted... here's the code again...

select

replace(ltrim(replace(data,'0',' ')),' ','0')

from @Temp

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)