How To Remove Leading Zeros

Hi
I need to remove leading zeros from a text string of numbers. For example:-
'000012' needs to be '12'
'000123' needs to be '123'
'012345' needs to be '12345'
'008000' needs to be '8000'
'01203' needs to be '1203'
There can be between 1 and 4 zeros at the front of the string. Removing all
zeros is not difficult using String Functions, but I only need to remove the
leading zeros. Does anyone have any suggestions?
Thanksif there are no blanks in the original string, then try this:
select replace(ltrim(replace('001100120','0',' ')),' ','0')
BTW "text string of numbers" might be a database design flaw|||Here is a brute force method.
select replace(ltrim(replace('000012','0',' ')),' ','0')
select replace(ltrim(replace('000123','0',' ')),' ','0')
select replace(ltrim(replace('012345','0',' ')),' ','0')
select replace(ltrim(replace('008000','0',' ')),' ','0')
select replace(ltrim(replace('01203','0',' ')),' ','0')
Payson
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Convert to int (if all the fields are numbers only of course)
declare @.value varchar(20)
select @.value ='000012'
select convert(int,@.value)
http://sqlservercode.blogspot.com/|||convert to int and back to varchar
e.g.
declare @.x table (col1 varchar(6))
insert into @.x
select '000012' union all
select '000123' union all
select '012345' union all
select '008000' union all
select '01203'
select convert(varchar, convert(int, col1)) as Stripped
from @.x
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Thanks guys, some good ideas here - I'll try them out in the morning.
You may be right, Alex, about the design flaw, but it's not my database!
"Trey Walpole" wrote:

> convert to int and back to varchar
> e.g.
> declare @.x table (col1 varchar(6))
> insert into @.x
> select '000012' union all
> select '000123' union all
> select '012345' union all
> select '008000' union all
> select '01203'
> select convert(varchar, convert(int, col1)) as Stripped
> from @.x
>
> andrew wrote:
>|||Once again, thanks for the suggestions, guys. Just for the record,
converting to Integer data type seems the easiest way to go:
SELECT CAST(field_name AS int) AS alias FROM table_name
"andrew" wrote:
> Thanks guys, some good ideas here - I'll try them out in the morning.
> You may be right, Alex, about the design flaw, but it's not my database!
>
> "Trey Walpole" wrote:
>

How to remove Hyperlink from column while exporting to excel

Hi,

I have one report with few of columns as hyperlinks leading to subreports. When report exported to Excel, columns which are hyperlinks exported and these hyperlinks still present in Excel and is not causing some problem as relative links are used in hyperlinks in main report. So, is there any way to remove hyperlinks from report while exporting to Excel?

I found a sort of work around for this.

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"

Dim sw As StringWriter = New StringWriter()
Dim htmlTextWriter As HtmlTextWriter = New HtmlTextWriter(sw)

ph.RenderControl(htmlTextWriter)
Dim sTemp As String = sw.ToString

sTemp = sTemp.Replace("href=", "")
Response.Write(sTemp)
Response.End()

Basically whatever you are giving the response.write to output, modify the string by taking out "href=". Even though there is still part of the <a> tag still there, excel seems to ignore it and just display the text.

Hope this works for you!!

And if it does work for, please mark this post as answer!!

Rob

sql