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 Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

add leading zero to date column?

Hello All,

None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.

Any ideas?

Thanks,

MikeMyk wrote:
> Hello All,
> None of the solutions I have found in the archives seem to solve my
> problem. I have a date column in my tables (stored as a char(10))

No, you have a char() column in your table that you store a string in
that is supposed to represent a date. It may or may not.

> which I would like to append a leading zero to for those dates that
> start with 9 or lower.

I'd recommend you actually make the column a date data type since that's
what it is for, but you can append like this:

CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn END

Now, there are tons of things wrong with what I just wrote in that it
assumes the first column will always be 0-9, which, given your specs
above, isn't garunteed. It assumes that there are no spaces in the first
character position. It assumes that if there is a 1 through 9 that the
length of that string plus the new '0' is still within 10 chars. All in
all, it is a crappy solution.

Make your data column type match your data and this problem goes away.

Zach

> Any ideas?
> Thanks,
> Mike|||Database Modeling Sin # 58 - Confusing Data Presentation with Data
Representation.

If you can't convert the column to its proper datatype (highly , greatly,
strongly, and "i mean it dude!" recommended),
add the following to a trigger on the table to insure that the format of the
string is consistent.

update mytable
set DateLikeCharColumn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharColumn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolumn = inserted.keycolumn

(substitute 110 with what ever style you need)

"nib" <individual_news@.nibsworld.com> wrote in message
news:304ot7F2qrbinU1@.uni-berlin.de...
> Myk wrote:
>> Hello All,
>>
>> None of the solutions I have found in the archives seem to solve my
>> problem. I have a date column in my tables (stored as a char(10))
> No, you have a char() column in your table that you store a string in that
> is supposed to represent a date. It may or may not.
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.
> I'd recommend you actually make the column a date data type since that's
> what it is for, but you can append like this:
> CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn
> END
> Now, there are tons of things wrong with what I just wrote in that it
> assumes the first column will always be 0-9, which, given your specs
> above, isn't garunteed. It assumes that there are no spaces in the first
> character position. It assumes that if there is a 1 through 9 that the
> length of that string plus the new '0' is still within 10 chars. All in
> all, it is a crappy solution.
> Make your data column type match your data and this problem goes away.
> Zach
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>

add a leading zero

Hi all,
I have a single table with one field:
CREATE TABLE [dbo].[class_number] (
[clientpartnercode] [char] (4) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO
Which contains values as: 014, 015, etc. However there are also values such:
311, 281 etc. I would like to synchronise these so that 311 becomes 0311 and
211 becomes 0211 etc but try as I might I have no idea how to do this withou
t
adding a ‘0’ to all values?
Is there any was this can be done?
Thanks
SamUse trigger to update value
Madhivanan|||UPDATE class_number SET clientpartnercode = '0' + LEFT(clientpartnercode, 3)
WHERE (LEFT(clientpartnercode, 1) <> 0)
I hope I have understood the question correctly - if there are 4-character
values of clientpartnercode they would have to be filtered out in the WHERE
clause as well.
Peter.
"Sam" wrote:

> Hi all,
> I have a single table with one field:
> CREATE TABLE [dbo].[class_number] (
> [clientpartnercode] [char] (4) COLLATE Latin1_General_BIN NOT NULL
> ) ON [PRIMARY]
> GO
> Which contains values as: 014, 015, etc. However there are also values suc
h:
> 311, 281 etc. I would like to synchronise these so that 311 becomes 0311 a
nd
> 211 becomes 0211 etc but try as I might I have no idea how to do this with
out
> adding a ‘0’ to all values?
> Is there any was this can be done?
> Thanks
> Sam
>|||That worked perfectly many thanks for your help.
"Peter Hyssett" wrote:
> UPDATE class_number SET clientpartnercode = '0' + LEFT(clientpartnercode,
3)
> WHERE (LEFT(clientpartnercode, 1) <> 0)
> I hope I have understood the question correctly - if there are 4-character
> values of clientpartnercode they would have to be filtered out in the WHER
E
> clause as well.
> Peter.
>
> "Sam" wrote:
>