Additional 0 to column data

Hi

I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg

12234

122334343

1234234567

how can i get it to look like this adding an 0 to the front to make the row 10 digits

0000012234

0122334343

1234234567

Thanks

SELECT REPLICATE('0',10 - LEN(CONVERT(VARCHAR(10),Column1))) + CONVERT(VARCHAR(10),Column1)

HTH,

Babu

|||

select right('000000000' + convert(varchar(10),ColumnName),10)

example

declare @.i int
select @.i = 12345

select right('000000000' + convert(varchar(10),@.i),10)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Alternatively perform the formatting in your front end application|||

If the number is to be stored as a number, then this is the best advice. I wouldn't suggest it if this is a value that will be used many different places and never be used in a math equation. For that I would store it in a character string and prepend the zeros.

The real problem comes in all of the different places it is used (reports, data warehouse,etc.) Someone has to format it, and you don't want the user to have to use some UI function to format it. You might do the formatting on the way to the DW and to a reporting data store, but to me it begs the question of the nature of the data. If the nature of the data is a code that happens to be all numbers (but would perform just as well in the application if it was not all numbers, ie 'asd02020' would not change the application as opposed to '000022020', then store it as a character, format it when you save it, and get it over with :)

Adding Serial No in the Query Resultset

Hi to All!
Can any one help me in generating serial no in the query result?
e.g. i write a query it gives me 500 rows.
select name,fathername from abc
name FatherName
Harry David
Sarah Nenry
.
.
.
.
i want that reslut should come like that
S-no name FatherName
1 Harry David
2 Sarah Nenry
.
.
.
.
500 Farid Masood
how can i add this sequence no in query i have no S-no column?
Regards
Thanx
*** Sent via Developersdex http://www.examnotes.net ***1. Create a temp table with an Identity column
2. Insert your values into the temp table
3. Select * From the temp table and return that resultset.
Greg Jackson
PDX, Oregon|||http://www.aspfaq.com/2427
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:uBPoaM2IGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi to All!
> Can any one help me in generating serial no in the query result?
> e.g. i write a query it gives me 500 rows.
> select name,fathername from abc
> name FatherName
> Harry David
> Sarah Nenry
> .
> .
> .
> .
> i want that reslut should come like that
> S-no name FatherName
> 1 Harry David
> 2 Sarah Nenry
> .
> .
> .
> .
> 500 Farid Masood
> how can i add this sequence no in query i have no S-no column?
> Regards
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Line numbering is an issue for the front end and has nothing to do with
the RDBMS. You can use a stinking dirty kludge with a proprietary
IDENTITY if you do not care about proper coding.
Have you ever had a software engineering course or read a book on
Software Engineering?|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...

> http://www.aspfaq.com/2427
'Be sure to read KB #186133 for Microsoft's official word'
(How to dynamically number rows in a SELECT Transact-SQL statement)
Applies to 2005...?..well do the people that write this stuff read BOL?
Perhaps they want to keep some things a secret:)
www.rac4sql.net|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138413114.379715.161790@.g47g2000cwa.googlegroups.com...
> Line numbering is an issue for the front end and has nothing to do with
> the RDBMS. You can use a stinking dirty kludge with a proprietary
> IDENTITY if you do not care about proper coding.
> Have you ever had a software engineering course or read a book on
> Software Engineering?
Would you feel better substituting ranking for line numbering?
And I was having a hard time using 'stinking dirty kludge' in
a sentence.
Did you write for 'Laugh In'? :)|||>> Would you feel better substituting ranking for line numbering? <<
That is fine; ranking as a rule while putting a number on the output of
an un-ordred cursor is absurd and unrepeatable.
But do you have a bigger problem using it in a program :)? You
should.
Two gags only. And I do not remember what they were. They paid $50
for the two.|||I've posted something in the private groups so hopefully they will update to
reflect all the new bits that they brought in for SQL Server 2005.
Eg...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@.nospam> wrote in message
news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
>
> 'Be sure to read KB #186133 for Microsoft's official word'
> (How to dynamically number rows in a SELECT Transact-SQL statement)
> Applies to 2005...?..well do the people that write this stuff read BOL?
> Perhaps they want to keep some things a secret:)
> www.rac4sql.net
>|||select row_number() over ( order by name ), name
from sys.objects
order by name
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ui7ET6LJGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I've posted something in the private groups so hopefully they will update
> to reflect all the new bits that they brought in for SQL Server 2005.
> Eg...
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
>

adding dummy column in Sql Query

Hi for all

How to Add a Dummy column in my Sqlquery like Serial Numbers
for example my query is
Select OrderNo,OrderDate,OrderValue,DummyColumn as Slno from OrderTable Where OrderValue>4000

( DummyColumn this column was not in Table )

so i want the result like

OrderNo OrderDate OrderValue Slno
---------------
Rd001 10/25/2001 $5000 1
Rd105 12/15/2002 $5657 2
Rd441 10/15/2001 $8000 3
Rd543 05/22/2002 $9040 4
Rd333 09/05/2002 $5170 5
Rd662 11/25/2002 $9556 6

How to Get this result. Is it possible with out using StoredProcedures and Temp Tables.

Please Give some sugessions.hii
please try this code in the northwind database, to get an idea

declare @.cmp varchar(100)
declare @.dc int
set @.dc = 0
declare cur_sor cursor for select companyname from customers
set @.dc = @.dc + 1
open cur_sor
fetch next from cur_sor into @.cmp
while @.@.fetch_status = 0
begin
select @.dc as dummycolmn,@.cmp as company
fetch next from cur_sor into @.cmp
set @.dc = @.dc + 1
end

close cur_sor
deallocate cur_sor

regards
ramki