March 13, 2008 at 1:06 pm
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.
March 13, 2008 at 1:18 pm
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
March 13, 2008 at 1:27 pm
Jay (3/13/2008)
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
March 13, 2008 at 1:38 pm
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
March 13, 2008 at 2:07 pm
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)
March 13, 2008 at 4:12 pm
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
March 13, 2008 at 10:05 pm
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
March 14, 2008 at 7:24 am
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
March 26, 2008 at 10:02 am
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
March 26, 2008 at 10:25 am
You could do the same thing, but probably cast as big int, for the numeric SKUs.
March 26, 2008 at 10:28 am
these days you can easily perform this kind of string processing by means of CLR Regular Expression functions:
http://qa.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);
}
}
March 26, 2008 at 11:07 am
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
March 26, 2008 at 4:13 pm
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.
March 26, 2008 at 4:17 pm
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?
March 27, 2008 at 7:45 am
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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply