Link to home
Create AccountLog in
Avatar of Metalteck
MetalteckFlag for United States of America

asked on

Remove the leading Zeros

I have a field in sql sever that is formatted as a char 250.
The field is called variable value and many different types of values are stored here.
There are some values in this table that have leading zeros.
Ex 00005658, 0000118467

I need to find a way to remove the leading zeroes without modifiying the other values stored in this field.

Can this be done?

Thanks
Avatar of Andr3wC
Andr3wC
Flag of Ireland image

Try the below:

* Maybe do a test first or backup your table before trying this as a precaution

UPDATE [dbo].[Table]
SET [Column] = REPLACE(LTRIM(REPLACE([Column], '0', ' ')), ' ', '0')
Avatar of Kent Olsen
Hi Metal,

Yes, it can be done.  :)  But there may be more to it than just dropping the leasing zero(s).

Is it possible that other data has leading zeros that you don't want dropped?  Perhaps there is a key, password, or other string that might start with a zero but not be numeric?

Anyway, just recasting is the easiest.

SELECT somecolumn, case (cast (somecolumn as integer) as varchar (20))
FROM mytable
WHERE isnumeric (somecolumn);

Open in new window




Good Luck,
Kent
This seems to work for me:

CREATE FUNCTION dbo.NoLeadZero (@input varchar(8000))
RETURNS varchar(8000) AS BEGIN

    DECLARE @test varchar(8000) = @input
    
    WHILE @test LIKE '0%'
        SET @test = SUBSTRING(@test, 2, LEN(@test))
        
    RETURN @test
    
END

GO

SELECT dbo.NoLeadZero('000123') --returns '123'
SELECT dbo.NoLeadZero('123000') --returns '123000'
SELECT dbo.NoLeadZero('Fred') --returns 'Fred'
SELECT dbo.NoLeadZero('0000') --returns ''

DROP FUNCTION dbo.NoLeadZero

Open in new window

Avatar of Haver Ramirez
Haver Ramirez

DECLARE @tbl AS TABLE ( fieldA VARCHAR(150) )

INSERT INTO @tbl ( fieldA )    VALUES ( '00040' ) 
INSERT INTO @tbl ( fieldA )    VALUES ( '00910' )
INSERT INTO @tbl ( fieldA )    VALUES ( '00110' )
INSERT INTO @tbl ( fieldA )    VALUES ( '001110' )
INSERT INTO @tbl ( fieldA )    VALUES ( '01510' )
INSERT INTO @tbl ( fieldA )    VALUES ( '00001' )

SELECT * FROM @tbl t

UPDATE x
SET
	x.fieldA = Replace((Ltrim(Replace(x.fieldA, '0', ' '))), ' ', '0') 
FROM @tbl x

SELECT * FROM @tbl t

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Guess I was really over-thinking that one :)
update table set field = LTRIM (field , '0') where isNumeric(field);
ehhh, skullnobrains, in my example you T-SQL gives me this:

Msg 174, Level 15, State 1, Line 31
The ltrim function requires 1 argument(s).
oups, i thought you could give an argument to trim
here is the hack : change zeros to spaces, then trim, then change spaces back to zeros. no problem since we only apply this to numeric data that do not contain spaces in the first place.

update table set field = replace(LTRIM (replace(field,'0',' ')),' ','0') where isNumeric(field);

btw, this is not t-sql. just a regular query

it may be easier to do with an ereg but i'm not fluent enough in msssql eregs
Or, to update that column in the table:


UPDATE dbo.tablename
SET column = SUBSTRING(column, PATINDEX('%[^0]%', column + ' '), LEN(column))
WHERE
    column LIKE '0%'
i guess the substring + patindex way is possibly a little faster than the double-replacement and trim, but they are functionnally equivalent

but this where clause will also make you trim stuff like '0some_text'

i believe a good way would be either of our SET clauses (@ScottPletcher's or mine) and a where clause with both clauses. this should make the query run faster if there are lots of non-matching entries, and even faster if the column is indexed
skull:

Your code also requires that no spaces appear in the actual data part of the column -- you were willing to assume that will always be the case, whereas I was not.

If spaces did appear in the data at some future point, your query would replace all of the spaces with zeros, whether they were leading spaces or not.

If you're going to use the REPLACE method, I suggest something like CHAR(7) (or some other never-used(?) char) rather than space.


As to the WHERE clause, why would it ever need multiple conditions?  Wouldn't the WHERE I used:

column LIKE '0%'

limit the rows updated to only those that needed modified, while allowing an available index to be used to determine such rows, if possible?!

Your code also requires that no spaces appear in the actual data part of the column -- you were willing to assume that will always be the case, whereas I was not.

given the where clause, i confirm we can safely assume none of the matched values contain a space. if you're careful about why i used a double-replace, you'll see that it would not work with anything other than a space.


Wouldn't the WHERE I used:

column LIKE '0%'

limit the rows updated to only those that needed modified

no, since i assumed we wanted to transform numerical columns only and the author explicitely said that not all columns contained numerical data. but like i said before, adding this 'like "0%"' to the where clause is likely to make the query run faster

---

are we here to help the author, or to fight between ourselves in order to know who is more right ? i'm trying to be courteous and see value in your comments, but this time, it is difficult
If this is right or not, pero Im glad for this discution
metalteck,

Did I get you right that the value to replace is somewhere in the middle of the column? Something like
   ABCD 0000123456 VERY USEFUL 0980760 CONTENT
? And you want to treat all occurances in the column, if more than one exist?
>>There are some values in this table that have leading zeros.
Ex 00005658, 0000118467<<
Does this represent a column containing two values or does it represent two values in your column? If the column contains multiple values, are they always comma separated? Is the 'Ex' part of the value(s) or was that shown to represent an Example?
>> if you're careful about why i used a double-replace, you'll see that it would not work with anything other than a space. <<

Yes, my char(7) comment was inaccurate, using REPLACE does require using space, which makes it very vulnerable to future content changes in the column.


>> given the where clause, i confirm we can safely assume none of the matched values contain a space. <<

I don't see that at all ... I assume you mean the WHERE clause with ISNUMERIC()??

But that comment did not come from the q author, and ISNUMERIC() can allow chars like $/,/./c/d/e thru.


>> the author explicitely said that not all columns contained numerical data <<

Yes.  To me, all the more reason to be careful about not arbitrarily removing all spaces from the value in the column.


I'm trying to get the best solution to the problem; that's all.  If something else works better that's great too.  But in fairness I believe any potential issues with any code should be pointed out before someone uses them in production.
@ScottPletcher

for the record, you need spaces because the idea is to use TRIM between the replaces. you can obviously replace "foo" with "bar" without spaces as well

you are right, this was an explicit assumption on my side from the very beginning

that's what i'm trying to do as well, but these 2 last posts are useless pecking. i think that anyway, the author has more than enough material to solve his issue (whatever the actual scope is) by now ; and i'm not really interested either in points or in arguing over what the author want's since the solutions will be trivial in any case.

no hard feelings but over and out
A solution without side effects can be trivial; one that produces inaccurate data later is not trivial, even if it may seem so initially.  When writing code, one must always make reasonable accomodation to avoid unnecessarily damaging data in the future.
your solution is not any better in this regard ( trim "0qq  " and not " 0033" ) and you're being uselessly agressive once more
we're not helping the author, nor gaining knowlege there
over and out
The author never mentioned " 0033".
 
IF that's needed, we can do this:

UPDATE dbo.tablename
SET column = SUBSTRING(column, PATINDEX('%[^ 0]%', column + ' '), LEN(column))
WHERE
    column LIKE '[ 0]%'

It's still not necessary to risk removing valid data from the rest of the string just to remove the leading zeros.
ScottPletcher, is true, the author nevers say "0033", but, he said,


many different types of values are stored here

then I suppose this value can exist ("0033"), also this "0a33" , also the number of zeros can vary. What the author mentions or not, is part of analysis. your own words.


A solution without side effects can be trivial; one that produces inaccurate data later is not trivial, even if it may seem so initially.  When writing code, one must always make reasonable accomodation to avoid unnecessarily damaging data in the future.

is part of analysis.
Avatar of Metalteck

ASKER

This resolved my problem exactly.
Thank you