SQL TRIM Leading zeros.

Hi Experts,

Ok in SQL how do I trim leading zeros?

So 000001 becomes 1

Thanks,
Dean
deanlee17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can convert it to a numeric type

cast('000001' as int)

if you need the result to remain a string, double cast it

cast(cast('000001' as int) as varchar)
0
James MurrellProduct SpecialistCommented:
SELECT (CAST Col1 AS int) AS Col1
0
tsnironeCommented:
This is varchar ?

Ie. if its varchar 6:

select substring(ColName, patindex('%[^0]%',ColName), 6)
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

deanlee17Author Commented:
Hmmm its actually a char field. Its part of a table join and its part of a substring, its currently...

SUBSTRING(dbo.AdmFormData.KeyField, 7, 10)
0
sdstuberCommented:
same idea except cast to char(10) instead of varchar

cast(cast(SUBSTRING(dbo.AdmFormData.KeyField, 7, 10) as int) as char(10))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
"best" - maybe, maybe not.

failure might be the best option.

  If the results are supposed to be numeric,  then trimming  '000xyz'  to 'xyz'  wouldn't be very helpful.
0
Scott PletcherSenior DBACommented:
Trying to cast it to an INT, as you're doing, would fail and cause the entire statement/batch to fail.

The other way, the command doesn't fail, and every other row could still be processed correctly.

You've just pointed out the main reason the other way is much, much better.
0
sdstuberCommented:
>>> It would fail and give an error trying to cast it to an INT, as you're doing.

exactly - I want it to do that.
I wouldn't have done a numeric conversion if I didn't want to force numeric data (int, was just an example,  I'd suggest float or money or something else if the data looked different)

>>> The other way, the command doesn't fail

yes/no .  That exact conversion might succeed, but that doesn't mean it really "worked"

if I'm using the values as if they were numeric, in addition to simply formatting them then, as noted before  '0000xyz'  trimmed to 'xyz'  isn't an improvement.  It's simply hiding the error so it'll show up in later processing.  I'd much rather have my errors show up early.

If, on the other hand,  non-numeric data is ok, then of course, use the pattern searching, or some other non-numeric conversion.
however, there is nothing to indicate one way or the other (except for the author's acceptance)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.