Parse to first non-zero

Hello,
If I have a field that contains varchar type data which looks like '00312'
How can I parse the field to just display '312' ?
Thanks!
Patricedeclare @.i varchar(5)
select @.i ='00123'
select convert(int,@.i)
Convert it to an int
http://sqlservercode.blogspot.com/
"Patrice" wrote:

> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Patrice,
One way...try:
SELECT CAST(<COLUMN> AS INT) FROM <TABLE>
HTH
Jerry
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Another way, which is a bit safer, though not tremendously:
declare @.char10 char(10)
set @.char10 = '0000001234'
select substring(@.char10, patindex('%[^0a-z]%',@.char10), len(@.char10))
I just look for the first non zero (and I tossed in letter just for kicks)
character and substring from there. No conversion needed, so if you need to
look for
'00000012BC'
it will return:
12BC
instead of crashing out.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||In a tiered architecture, display is always done in the front end and
NEVER in the database. This more fundamental that just SQL; don't you
remember this from your first Software Engineering course?
The stinking dirty kludge is to CAST() the string to INTEGER. But
since you did not know that a column is not anything like field, you
probably have serious design problems in many place where you have
string and other data types.|||So to quote Bobby Boucher's mamma in the Waterboy, "CAST IS THE DEVIL!!!"
:-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>|||That was Kathy Bates! I had forgotten that Movie!
But I do remember my wife and I going to some movie years ago (forgot
the title) where George Cluney as an Army Officier and Nicole Kidman as
an atomic scientist trying to find an atomic bomb. When Kidman came on
the screen we both looked at each other and said "That [atomic bomb
expert] should have been Kathy Bates!"
But getting back from the flashback, the problem with CAST() is that
host languages do not all agree on how to handle SQL data types. Doing
the CAST() in SQL to feed it to an unknown host language conversion
adds overhead and makes results lesspredictable when someone looks at
the schema to figure out what happened. A version of the copy of a
copy of a copy.. problem.|||Yeah, I don't know why didn't think to say this. I got more interested in
just doing something other than cast. The word "display" should have jogged
something in my brain.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>