Adding a leading character to a string

I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.

It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.

I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).

And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.SELECT Convert(CHAR(7), GetDate(), 121)
Use the date expresion of your choice instead of GetDate()

-PatP|||I use this ;)

create function dbo.FN_INT_TOSTRING(@.codigo int, @.length int) returns varchar(10)
begin
declare @.res varchar(10)
set @.res = cast(@.codigo as varchar)
while len(@.res) < @.length
begin
set @.res = '0' + @.res
end
return @.res
end|||What about a generic function like:

Right('0' + cast(@.MyInt as varchar(2)), 2)

But Pat really has the best answer here.

Regards,

hmscott|||Thanks everyone. Simpler than I first thought! I'll play with it at work on Monday morning (9 PM over here now).

PS. Sorry for replying from another alias - I now realize I'm logged on as Coolberg from my work and as Nabucco from my home computer. I'll correct that. ;-)
DS.|||So finding an answer at 21:00 on Friday night doesn't inspire you to run right down to the office to try it ? Well, what kind of geek are you anyway ? Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

-PatP|||select convert(varchar(2),getdate(),101)|||> Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

I missed the beer; the beer shop here closes at 6 PM ;-)|||select convert(varchar(2),getdate(),101)

I'll test this one tomorrow.

By the way, SELECT Convert(CHAR(7), GetDate(), 121) didn't work.

I tried Right('0' + cast(@.MyInt as varchar(2)), 2) today, it worked fine.|||waddyamean it didn't work...of course it worked...read the hint sticky at the top of the forum|||The problem is, I'm doing a (for example)

select convert(varchar(2),datepart(mm,'2006-01-30'),101)

where you'll still get "1" since datepart() returns "1" , not "01".|||select right(convert(char(7),yourdate,120),2)|||And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.select convert(char(7),yourdate,120)|||Select convert(varchar(2),convert(datetime,'2006-01-30'),101)|||Thanks everybody!

Add spaces to a string value in a cell

Hi,

I am trying to add spaces to a string value in a cell but when I run the report the value is trimmed.
Any ideas?
Here is the expression I am using:
=Fields!strVal.Value & " "

Thanks,
Igor

Igor,

Try using a calculated field on your dataset, In the expression, use =" number of spaces wanted" save field name like Space5. You can then use this field in your expressions.

Ham

|||I tried and it still comes out trimmed.|||

Igor,

Can you wrap a LEN around your expression? I used this to verify that the spaces where actually there. If the spaces are there then you made have some other issue.

Ham

|||the Len function returns the correct number of characters (including the empty spaces) but the spaces just don't show up.|||

Igor,

What type of formatting are you trying to accomplish? Are you trying to align text, columns?

Ham

|||I am trying to move some of the values a little to the left but still keeping them right-justified.|||
Igor,
You can try a couple of different things (what you have should work, but if not I can only suggest these)....

=Cstr(Fields!strVal.Value) & Space(5)
or
=Cstr(Fields!strVal.Value) + Space(5)

In Data you could try Cast or Convert on the field to force it to a certain number of characters as well.|||

Igor,

Why not use your "Left-Alignment" properties on your textbox?

Ham

|||

Hello Igor,

Can you try something like this in your field's expression:

=Fields!FIELD1.Value & StrDup(5, " ")

Hope this helps.

Jarret

Add parameters

I need to add parameters to my SQL string, like Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd, I tried to Dim variables but it caused an error. Can anyone help me with this?

Thank You,

Sub BindDataCurrent()
Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd"
'MyCommand.Parameters.Add("@.HStart", SqlDbType.VarChar, 80).Value = HistoryStartText.Text
'MyCommand.Parameters.Add("@.HEnd", SqlDbType.VarChar, 80).Value = HistoryEndText.Text
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd"
Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End SubHi,
Why you do not use this select?

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= " & HistoryStartText.Text & " AND [EndDate] <= " & HistoryEndText.Text

Your Code:
Sub BindDataCurrent()

ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= " & HistoryStartText.Text & " AND [EndDate] <= " & HistoryEndText.Text

Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End Sub

Add leading Zeros to integer?

I need to cast an integer to a string to append to another string for a barcode.
How can I get 1 -> '0001' OR 100 -> '0100'
Any suggestions?
Mike BAs long as they are all positive numbers, you can use:SELECT Replace(Str(1, 20), ' ', '0')If you need to cope with negative numbers, I'd recommend using a short function... It is cleaner.

-PatP|||As long as they are all positive numbers, you can use:SELECT Replace(Str(1, 20), ' ', '0')If you need to cope with negative numbers, I'd recommend using a short function... It is cleaner.

-PatP
Thanks that was what I was looking for.

Mike B

Add constant variable to report project

I'd like to add a constant string variable to my report project.
Something like stCOMPANY_NAME. I'd like the reports to be portable to some
of our other plants so it would be nice so be able to change the company
name on the header of all the reports by just changing one constant
somewhere in the project. I know this could be done at the report level
(putting a constant in the code block for the report), but can it be done at
the project, public level ?I'm sure there must be an OLEDB/ODBC driver for reading in text files.
Stick in a text file and read it in as a dataset.
If you use the OLEDB provider for ODBC drivers, you can read from Excel.
Hope that helps.
Chris
D Witherspoon wrote:
> I'd like to add a constant string variable to my report project.
> Something like stCOMPANY_NAME. I'd like the reports to be portable
> to some of our other plants so it would be nice so be able to change
> the company name on the header of all the reports by just changing
> one constant somewhere in the project. I know this could be done at
> the report level (putting a constant in the code block for the
> report), but can it be done at the project, public level ?|||You can make Company name as parameter.
Kiran
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:%23$PMtNocFHA.3040@.TK2MSFTNGP14.phx.gbl...
> I'm sure there must be an OLEDB/ODBC driver for reading in text files.
> Stick in a text file and read it in as a dataset.
> If you use the OLEDB provider for ODBC drivers, you can read from Excel.
> Hope that helps.
> Chris
> D Witherspoon wrote:
>> I'd like to add a constant string variable to my report project.
>> Something like stCOMPANY_NAME. I'd like the reports to be portable
>> to some of our other plants so it would be nice so be able to change
>> the company name on the header of all the reports by just changing
>> one constant somewhere in the project. I know this could be done at
>> the report level (putting a constant in the code block for the
>> report), but can it be done at the project, public level ?
>

Add a number to string. (conversion problem)

Lets say I have a table named Projects with the data below.
Y07001
Y07002
Y07003
Y07010
Y07011
I want to pick up the last 3 numbers and add a one to it so.

Declare @.Countas varchar(3)SELECTTop 1 @.Count=Right(Project, 3)+1FROM ProjectsOrderby IDdesc

SELECT @.Count

This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12

What you want is probably this?

SELECT

RIGHT('000'+Convert(varchar,@.Count),3)

Add a line feed to a string

How do you add a line feed to string variable?
="Activate Item " + Fields!item_id.Value + \n + Fields!
item_desc.Value
Thanks
BobRS uses .Net
"foo" + System.Environment.NewLine