formatting numbers

Hi,
I was wondering whether there is a way to format numbers in sql as below.
Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
So that 100 = 0100, 0999, 0022, 1222, etc...
Does anyone know how to do this?
Thanks
DhruvTry:
declare @.x int
set @.x = 121
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 100
select right('0000' + cast(@.x as varchar(4)), 4) num
set @.x = 999
select right('0000' + cast(@.x as varchar(4)), 4) num
- Vishal|||Try this:
declare @.i smallint
set @.i = 23 -- or ...
select RIGHT('000' + CAST(@.i AS varchar(4)), 4)
HTH
Vern
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||You could do soemthing like this in T-SQL:
declare @.i int
set @.i = 122
select right('0000' + cast(@.i as varchar(4)), 4)
But I'd question why you'd want to do this on SQL Server
side? This is best done at the clietn side in whatever
language you may be using. Most languages have good
support for this type of string manipulation.
Linchi
>--Original Message--
>Hi,
>I was wondering whether there is a way to format numbers
in sql as below.
>Meaning I was to convert 1 to 0001 (with maximum of 4 as
the length)
>So that 100 = 0100, 0999, 0022, 1222, etc...
>Does anyone know how to do this?
>Thanks
>Dhruv
>.
>|||SELECT RIGHT('0000'+RTRIM(1), 4)
However, I agree with Linchi. Do your "prettifying" of the data where it
belongs, in the presentation tier.
> I was wondering whether there is a way to format numbers in sql as below.
> Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> So that 100 = 0100, 0999, 0022, 1222, etc...
> Does anyone know how to do this?
> Thanks
> Dhruv|||I don't know that I completely agree with that, though I see the point.
Consider the case where you use the same data 10 places, using the same
stored procedure. The formatting would be easier done in the procedure,
rather than the UI.
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GXS0IrjDHA.2312@.TK2MSFTNGP12.phx.gbl...
> SELECT RIGHT('0000'+RTRIM(1), 4)
>
> However, I agree with Linchi. Do your "prettifying" of the data where it
> belongs, in the presentation tier.
>
> > I was wondering whether there is a way to format numbers in sql as
below.
> >
> > Meaning I was to convert 1 to 0001 (with maximum of 4 as the length)
> >
> > So that 100 = 0100, 0999, 0022, 1222, etc...
> >
> > Does anyone know how to do this?
> >
> > Thanks
> >
> > Dhruv
>|||> Consider the case where you use the same data 10 places, using the same
> stored procedure.
In most client applications, you can have a common formatting routine.|||Awesome
Thanks
"Vishal Parkar" <_vgparkar@.yahoo.co.in> wrote in message news:<#LU2$ArjDHA.744@.tk2msftngp13.phx.gbl>...
> Try:
> declare @.x int
> set @.x = 121
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 100
> select right('0000' + cast(@.x as varchar(4)), 4) num
> set @.x = 999
> select right('0000' + cast(@.x as varchar(4)), 4) num

formatting in sql server

hi

below is my query

select * from guest.tbl1

i m getting result like this

id score

1 50
2 100

3 150

4 200

but i want this output like this

1 50 2 100

3 150 4 200

how to do this?

thanx

Where you want to display this record?In a web page or in the SQL server o/p?|||in sql server o/p|||

Code Snippet

CREATE TABLE #TMP1(ID INT IDENTITY (1,1),COL1 INT,COL2 INT)
CREATE TABLE #TMP2(ID INT ,COL3 INT,COL4 INT)

INSERT INTO #TMP1
(
COL1
,COL2
)
SELECT id
,val
FROM tbl1

INSERT INTO #TMP2(ID,COL3
,COL4
)
SELECT ID
,COL1
,COL2 FROM #TMP1 WHERE ID%2=1

SELECT * FROM #TMP1
SELECT * FROM #TMP2
SELECT T2.COL3
,T2.COL4
,T1.COL1
,T1.COL2 FROM #TMP1 T1 JOIN #TMP2 T2 ON T1.ID = T2.ID + 1
GO
DROP TABLE #TMP1
DROP TABLE #TMP2

|||

Afraid to believe the ID value , It may have gaps rite?

Code Snippet

Create Table #data (

[id] int ,

[score] Varchar(100)

);

Insert Into #data Values('1','50');

Insert Into #data Values('2','100');

Insert Into #data Values('5','150');

Insert Into #data Values('6','200');

Insert Into #data Values('9','200');

Insert Into #data Values('10','200');

If you use SQL Server 2005,

Code Snippet

;WITH CTE

as

(

Select

*,

Row_Number() Over(Order By ID) RID

From

#data

)

Select up.id,up.score,dwn.id,dwn.score from CTE up

left outer join CTE dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

If you use SQL Server 2000,

Code Snippet

Declare @.Table Table(

[id] int ,

[score] Varchar(100) ,

[RID] int identity(1,1)

)

Insert Into @.Table

Select Id,Score From #Data Order By 1;

Select up.id,up.score,dwn.id,dwn.score from @.Table up

left outer join @.Table dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

Formatting data with FOR XML and sp_makewebtask?

Hi all,

I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

Cheers,
Christian

-------

-- sp_makewebtask --
EXEC sp_makewebtask
@.outputfile = c:\temp\output.xml,
@.query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@.templatefile ='c:\temp\template.tpl'

-- c:\temp\template.tpl --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Data>

-- c:\temp\output.xml --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
</Data>

-- Prefered XML output --
<?xml version="1.0" encoding="UTF-8"?>
<Data Subdata3="1">
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
</Subdata>
</Data>Nevermind, solved it using FOR XML EXPLICIT. :D

/Christian

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
See other post..
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:

> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatted text in a column;

Hi All ,

Is it possible to insert the below text into a column and retrieve it in the same format ?

Thanks,

Hari Haran Arulmozhi

TEXT :

CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI

================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600

RECEIVED ADVANCE :Rs.10000u will need to take care of the formatting in the front end.|||Well, strictly speaking you CAN do the formatting on the back end. What harshall means is that you SHOULD do the formatting on the front end.

Formatting is a presentation issue and does not fall within the scope of a database server.

Read up on the datetime datatype and how it is stored in Books Online.|||Thanks Harshal & Blindman !!

Format number

I am doing some simple arithmatic for a query that I am working on. What I have below describes the arithmatic that is taking place withing a portion of the query:

select(o.sales_val/o.qty_sales) as 'Unit_Price', (o.acctng_cost_val/o.qty_sales) as 'Unit_Cost'
from opcsahf as o
go

The division is correct. My only issue is that the results for 'Unit Price' or 'Unit Cost' may be formatted like example: 4.25000000.

How can I have my nubers show up with only to 2 decimal places instead of all the zeros at teh end?It really is a presentation layer issue..

What are the datatypes of the columns

Did you just try CONVERT(decimal(15,2),....|||Brett,

I went ahead and tryed the convert(decimal(15,2) and everything worked fine. This was the results for the statement:

select convert(decimal (15,2),o.sales_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Price', convert(decimal (15,2),o.acctng_cost_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Cost'
from opcsahf as o
go

Thanks alot Brett. It did the trick.

Originally posted by Brett Kaiser
It really is a presentation layer issue..

What are the datatypes of the columns

Did you just try CONVERT(decimal(15,2),....

Format Dates in TSQL - My code below

Howdy,
Is this proc:
SELECT ID, CID, dtDate, strTime, dtRSVP,
CASE
WHEN dtRSVP > 1/1/1900 THEN
'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
ELSE
NULL
END AS niceRSVP
FROM tblDates
the best solution to get these results:
ID EID dtDate strTimes
dtRSVP niceRSVP
3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
00:00:00 RSVP by 11/25/2005
4 4 2005-12-02 00:00:00 12-4 PM
1900-01-01 00:00:00 NULL
Basically, I want the dates formatted like 11/25/2005, and empty date fields
to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP fields, but I
made the niceRSVP field to compensate.
Please advise!
Thanks!
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.comDavid,
Is it required to be a NULL or the text NULL. If the latter, the following
will work. See as example:
CREATE TABLE TBL_DATES
(ID INT NOT NULL,
DTVAL DATETIME )
GO
INSERT TBL_DATES(ID,DTVAL)
VALUES(1, '10/12/05')
INSERT TBL_DATES(ID,DTVAL)
VALUES(2, '')
INSERT TBL_DATES(ID)
VALUES(3)
GO
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
> Howdy,
> Is this proc:
> SELECT ID, CID, dtDate, strTime, dtRSVP,
> CASE
> WHEN dtRSVP > 1/1/1900 THEN
> 'RSVP by ' + CAST(MONTH(dtRSVP) as varchar(15)) + '/' + CAST(DAY(dtRSVP)
> as varchar(15)) + '/' + CAST(YEAR(dtRSVP) as varchar(15))
> ELSE
> NULL
> END AS niceRSVP
> FROM tblDates
> the best solution to get these results:
> ID EID dtDate strTimes dtRSVP
> niceRSVP
> 3 4 2005-12-01 00:00:00 1:00 PM to 3:00 PM 2005-11-25
> 00:00:00 RSVP by 11/25/2005
> 4 4 2005-12-02 00:00:00 12-4 PM 1900-01-01 00:00:00
> NULL
> Basically, I want the dates formatted like 11/25/2005, and empty date
> fields to be NULL not 1/1/1900. Same goes for the dtDate and dtRSVP
> fields, but I made the niceRSVP field to compensate.
> Please advise!
> Thanks!
> --
> David Lozzi
> Web Applications Developer
> dlozzi@.(remove-this)delphi-ts.com
>
>|||Or this with NULL.
SELECT CASE
WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN
NULLIF('01/01/1900',DTVAL) ELSE
CONVERT(VARCHAR(10),DTVAL,101)
END AS 'DATE'
FROM TBL_DATES
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Or even more succinctly:
SELECT
niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
FROM
tblDates
A null value in dtRSVP will pass through CONVERT as a null, and adding
anything to a null equals null.
JR
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> David,
> Is it required to be a NULL or the text NULL. If the latter, the
> following will work. See as example:
> CREATE TABLE TBL_DATES
> (ID INT NOT NULL,
> DTVAL DATETIME )
> GO
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(1, '10/12/05')
> INSERT TBL_DATES(ID,DTVAL)
> VALUES(2, '')
> INSERT TBL_DATES(ID)
> VALUES(3)
> GO
> SELECT CASE
> WHEN CONVERT(VARCHAR(10),DTVAL,101) = '01/01/1900' THEN 'FALSE NULL' ELSE
> CONVERT(VARCHAR(10),DTVAL,101)
> END AS 'DATE'
> FROM TBL_DATES
>
> HTH
> Jerry
> "David Lozzi" <DavidLozzi@.nospam.nospam> wrote in message
> news:elUhO1eyFHA.3096@.TK2MSFTNGP10.phx.gbl...
>|||Jim,
How does this code account for the NULL if 01/01/1900?
HTH
Jerry
"Jim Ross" <jratwork_at_hotmail.com@.nowhwere.com> wrote in message
news:OMTaUPfyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or even more succinctly:
> SELECT
> niceRSVP = 'RSVP by ' + CONVERT(varchar(15),dtRSVP,101)
> FROM
> tblDates
> A null value in dtRSVP will pass through CONVERT as a null, and adding
> anything to a null equals null.
> JR
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e634GCfyFHA.2064@.TK2MSFTNGP09.phx.gbl...
>