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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tsnironeCommented:
This is varchar ?

Ie. if its varchar 6:

select substring(ColName, patindex('%[^0]%',ColName), 6)
0
 
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:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.