Remove leading zeros and add spaces at the end

Question

Post reply

Remove leading zeros and add spaces at the end

Viewing 11 posts - 16 through 25 (of 25 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Sergei Zarembo (3/26/2008)

Jeff,

i agree in this simple replace it would be an overkill. But replace pattern can change by the minute. Wouldn't it be more convenient to have one [or more] replace function that would take 'yourstring' as param1, 'matchPattern' ans param2 and 'replacePattern' as param3? This would allow for more compact code at the least. Plus it's more flexible and readable.

As for your previous remark in the thread, sometimes you do need to do heavy text processing in SQL Server: I've been in situations like this. Often tt stems from a faulty architecture of the entire production system, I must admit.

Sure... but if you're going to do that well, a full blown RegEx Replace such as what I've seen Matt Miller do would be the best.

However, and Matt will vouch for this because of the extreme testing he and I have done in the past, if you have performance in mind, T-SQL solutions will beat any CLR solution 99 percent of the time and you don't have to have separate documentation and source control for the CLR's. 😉

--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)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jeff Moden (3/27/2008)

Heh... no, you're right, Matt. I was just anticipating the next possible question (I should probably get out of the habit) which usually turns up as either "How would I remove the leading zeroes for something bigger than an Int or BigInt" or "How would I remove the leading zeroes if there were some non-digit characters in the string?"

Yup...I hear ya there...There's definitely that pattern...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jim.poindexter-840611 SSC Rookie Points: 45 More actions · Answer 3

jim.poindexter-840611

SSC Rookie

Points: 45

August 5, 2008 at 4:15 pm

#853538

Thanks for the solution to removing leading zeros. Worked great.

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

Thanks for the feedback, Jim.

--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)
Intro to Tally Tables and Functions

Jason Scott Kelley SSC Enthusiast Points: 104 More actions · Answer 5

Declare @MyStr char(20)

Set @MyStr = '00004567890'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

What this does is to find the first non-zero character in the field and then substring from there. It also works if you have a dash imbeded in the field.

Declare @MyStr char(20)

Set @MyStr = '0000-45670'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

As for formatting, not every application involves a GUI. I solved this problem because I was receiving a file from a vendor that I had to import into the database. They passed negative values as mentioned above. Just doing a conversion wasn't good because it failed so I had to strip the zeros to get the conversion to work. Enjoy.

chris.fauvel Valued Member Points: 65 More actions · Answer 6

if your skus are like

00supra12-23

0012-34

00001234

then casting to int will not work since 'supra' and '-' are not numeric.

I precisely need to do the same thing , remove the leading zeros to get

supra12-23

12-34

1234

thankfully my data only has 6 records int he resultset that need this done to...I'm just going to update them. The remaing 23K rows had leading zeros and could be cast to int just fine.

if anyone one has a simple solution in the select statement to strip off leading zeros when the value is non-numeric please share.

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Jeff Moden (3/27/2008)

For example...

DECLARE @Sku NVARCHAR(500) SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000' SELECT @Sku AS Original, SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

Heh... no, you're right, Matt. I was just anticipating the next possible question (I should probably get out of the habit) which usually turns up as either "How would I remove the leading zeroes for something bigger than an Int or BigInt" or "How would I remove the leading zeroes if there were some non-digit characters in the string?"

chris.fauvel (9/3/2009)

if your skus are like

00supra12-23

0012-34

00001234

then casting to int will not work since 'supra' and '-' are not numeric.

I precisely need to do the same thing , remove the leading zeros to get

supra12-23

12-34

1234

thankfully my data only has 6 records int he resultset that need this done to...I'm just going to update them. The remaing 23K rows had leading zeros and could be cast to int just fine.

if anyone one has a simple solution in the select statement to strip off leading zeros when the value is non-numeric please share.

Well, that's disappointing... Jeff anticipated this question 18 months ahead of time and it STILL didn't stop it :-D.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Garadin (9/3/2009)

Jeff anticipated this question 18 months ahead of time and it STILL didn't stop it :-D.

BWAA-HAAA!!!! Ironically, the same thing happens at work!

Man, thanks for both the observation and the laugh Seth. 😀

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

chris.fauvel (9/3/2009)

if your skus are like

00supra12-23

0012-34

00001234

then casting to int will not work since 'supra' and '-' are not numeric.

I precisely need to do the same thing , remove the leading zeros to get

supra12-23

12-34

1234

thankfully my data only has 6 records int he resultset that need this done to...I'm just going to update them. The remaing 23K rows had leading zeros and could be cast to int just fine.

if anyone one has a simple solution in the select statement to strip off leading zeros when the value is non-numeric please share.

Are you all set, Chris?

--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)
Intro to Tally Tables and Functions

chris.fauvel Valued Member Points: 65 More actions · Answer 10

chris.fauvel

Valued Member

Points: 65

September 4, 2009 at 8:55 am

#1049444

"am I all set?"

yes/no

since there were only 6 skus like that I just updated them, but I would love to have an elegant solution, that is easy.

Thanks

Chris

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

chris.fauvel (9/4/2009)

"am I all set?"

yes/no

since there were only 6 skus like that I just updated them, but I would love to have an elegant solution, that is easy.

Thanks

Chris

Like Seth said, anticipated 18 months ago on this thread... here's the code example. Just change @SKU to your column name...

DECLARE @Sku NVARCHAR(500)



SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000'

SELECT @Sku AS Original,

SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

--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)
Intro to Tally Tables and Functions