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:
>

actual creator of a database

Does anyone know how to obain the actual creator of a database by using TSQL or SP? I need to know actual Login not DBO.

Thanks.

You can access the system table directly:

select suser_sname(sid)
from master..sysdatabases
where name = 'tempdb'

There maybe an inbuilt T-SQL function that would tell you this, which would be a preferable way of getting the info.

HTH!

|||

You cannot do this (exactly) that I know of. You can get the owner of the database using the code in the other post:

select suser_sname(sid)
from master..sysdatabases
where name = 'tempdb'

or

select name, suser_sname(owner_sid)

from sys.databases

The owner can be changed though using: sp_changedbowner, so you are actually getting the current owner. That may be good enough for your needs, but it is a caveat you should understand.