Remove leading zeros and add spaces at the end

Question

Post reply

Remove leading zeros and add spaces at the end

Jay1234

SSCommitted

Points: 1624
More actions
March 13, 2008 at 1:06 pm

#184957

I have string data in a field.
0000010011
0020010234
0102020202
I would like to remove all the leading zeros and add spaces at the end so that the length of data would be 15.
Please help. Thanks.

Viewing 15 posts - 1 through 15 (of 25 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

The only way I can think of to do this is with a loop.

while exists

(select *

from dbo.Table

where Field like '0%')

update dbo.Table

set Field = right(field, len(field)-1) + ' '

where Field like '0%'

There might be a single-update command for this, but I think it would be so incredibly complex, since it would have to determine how many zeroes there are for each row, that it would be worse.

Try the above. See if it works for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 2

Jay (3/13/2008)

I have string data in a field.

0000010011

0020010234

0102020202

What's the datatype of you column ?

I suppose it is char(10).

In that case you would need :

[Code]

alter table yourtable

set yourcolumn char(15)

go

[/Code]

Keep in mind the consequences for removing the leading zeroes !

Maybe you'd be better off changing the datatype to integer !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

GSquared SSC Guru Points: 260824 More actions · Answer 3

ALZDBA!! Thank you! I knew I was missing an easier way to do this.

cast(cast(field as int) as char(15))

That does the whole thing. (I knew my loop idea was wrong. Just a brain deadlock issue. 🙂 )

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jay1234 SSCommitted Points: 1624 More actions · Answer 4

Thanks GSquared and ALZDBA. That was a perfect solution.

I found a way to do in VB.NET also 🙂

CStr(FieldName.TrimStart(CChar("0"))).PadRight(15)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

There might be a single-update command for this, but I think it would be so incredibly complex, since it would have to determine how many zeroes there are for each row, that it would be worse.

Not really if you convert it into a number 😛

UPDATE #T

SET nbr = RIGHT(SPACE(10) + CAST(CAST(nbr AS INT) AS VARCHAR(10)),10)

FROM #t

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

I really want to know why you're formatting numbers in SQL... that should be done in the GUI if there is one... and it looks like there is one. 😉

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 7

Adam Haines (3/13/2008)
There might be a single-update command for this, but I think it would be so incredibly complex, since it would have to determine how many zeroes there are for each row, that it would be worse.
Not really if you convert it into a number 😛
UPDATE #T
SET nbr = RIGHT(SPACE(10) + CAST(CAST(nbr AS INT) AS VARCHAR(10)),10)
FROM #t

As per my post from about 2 before this one, yep. Exactly! Just wasn't thinking clearly on this one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

seajoker Say Hey Kid Points: 700 More actions · Answer 8

I looking for the somewhat of the same results. I have a colunm that is nvarchar(18). It has a list of SKUs. Some SKUs are like SUPA123 while others are like 000000001233567. I would like to take off the leading zeros. The cast function to int doesn;t work because of the length of some of the SKUs.

Any help would be great!

seajoker

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

You could do the same thing, but probably cast as big int, for the numeric SKUs.

My blog: http://jahaines.blogspot.com

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 10

these days you can easily perform this kind of string processing by means of CLR Regular Expression functions:

http://www.sqlservercentral.com/articles/Development/clrintegration/1967/

You need to create a UDF CLR function that would match the all leading 0s and then replace them with an empty string.

in C#:

using System.Text.RegularExpressions;

string MyStringToWorkOn = "0000000123";

string MyCleanedString = "";

//do cleaning by Regex here

MyCleanedString = Regex.Replace(MyStringToWorkOn, "^0+", "")

//returns MyCleanedString = "123"

second argument of the function is your Matching Pattern: meaning *Match all leading )s from the start of the string*

third argument is a Replacement Pattern: an empty string in this case.

to write the CLR regex functions go to the link above...

something like :

using System.Data.SqlTypes;

using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlBoolean RegExValidate(

SqlString expressionToValidate, SqlString regularExpression)

{

Regex regex = new Regex(regularExpression.Value);

return regex.IsMatch(expressionToValidate.Value);

}

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

Ummmm.... you don't really need a CLR or external RegEx to do something so simple as to remove leading zeros. For example...

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)

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 12

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.

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

Isn't casting to a number (as was previously mentioned) the easiest AND fastest way to do this? Even if you have to cast it back to a char(15)? I'm all for heavy text parsing when is necessary, but this just doesn't look like place for it?

Or have we moved beyond the initial request now?

Am I asking too many questions in this post? How about now?:hehe:

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 14

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?"

--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)