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'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @.testFloat float
select @.testFloat = 1000000.12

select convert(varchar(100),@.testFloat) -- gives 1e+006
select cast(@.testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@.testFloat as money)) -- gives
1000000.12

select @.testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@.testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
DaveTry specifying the desired precision and scale on your decimal/numeric
declaration:

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.posting.google.co m...
> I'm trying to find a way to format a FLOAT variable into a varchar in
> SQL Server 2000 but using CAST/CONVERT I can only get scientific
> notation i.e. 1e+006 instead of 1000000 which isn't really what I
> wanted.
> Preferably the varchar would display the number to 2 decimal places
> but I'd settle for integers only as this conversion isn't business
> critical and is a nice to have for background information.
> Casting to MONEY or NUMERIC before converting to a varchar works fine
> for most cases but of course runs the risk of arithmetic overflow if
> the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
> knows of an easy way to test whether overflow will occur and therefore
> to know not to convert it then that would be an option.
> I appreciate SQL Server isn't great at formatting and it would be far
> easier in the client code but code this is being performed as a
> description of a very simple calculation in a trigger, all stored to
> the database on the server side so there's no opportunity for client
> intervention.
> Example code:
> declare @.testFloat float
> select @.testFloat = 1000000.12
> select convert(varchar(100),@.testFloat) -- gives 1e+006
> select cast(@.testFloat as varchar(100)) -- gives 1e+006
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> 1000000.12
> select @.testFloat = 12345678905345633453453624453453524.123
> select convert(varchar(100),cast(@.testFloat as money)) -- gives
> arithmetic overflow error
> select convert(varchar(100),cast(@.testFloat as numeric)) -- gives
> arithmetic overflow error
> Any suggestions welcome...
> Cheers
> Dave|||Dan and Igor, both examples worked great, thanks very much.

SELECT CONVERT(varchar(100), CAST(@.testFloat AS decimal(38,2)))
SELECT STR(@.testFloat, 38, 2)

Cheers
Dave

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
>

Format numeric to display dollar value $1,000.00

Hi,
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>

format for Convert

Hello,

SELECT name + '- ' + CONVERT(varchar,amt) as ddlCap from myTable

How can I get amt be like "#0.00" format in ddlCap?

Hi,

Please run the below statement to see if it is what you want...

SELECT
amt,
CONVERT(varchar(20),amt,0),
CONVERT(varchar(20),amt,1)
FROM myTable

Eralper
http://www.kodyaz.com

Format date time

declare @.d smalldatetime
declare @.e varchar(8)
set @.d='1/8/2005'
set @.e=.........?
I wanna set @.e with format 'YYYYMMDD' of @.d. How?One method:
SET @.e = CONVERT(varchar(8), @.d, 112)
You also might consider using the YYYYMMDD format in your date literal
strings in order to avoid ambiguity.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:u9UckoYkFHA.1044@.tk2msftngp13.phx.gbl...
> declare @.d smalldatetime
> declare @.e varchar(8)
> set @.d='1/8/2005'
> set @.e=.........?
> I wanna set @.e with format 'YYYYMMDD' of @.d. How?
>

Format a number to '00'

Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
SteveTry:
select
replace (str (DatePart(m,[bhDate]), 2), ' ', '0')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:CA25F627-E9E8-4147-8D3F-EAABF2D71441@.microsoft.com...
Hi,
I am pulling the month from a date like this:
Cast(DatePart(m,[bhDate]) as varchar)
if it comes back 12, I get 12, if it comes back as the 7th month, I want to
get '07'.
I normally use the FORMAT function in VBA, but having trouble finding how to
do it in T-SQL
Any thoughts,
Steve|||Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
Cast(DatePart(m,[bhDate]) as varchar)
--
Thanks & Rate the Postings.
-Ravi-
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||Here is another approch which could be shaved down to smaller set of command
s
but I thought the long version would help with concept.
DECLARE @.v_Month VARCHAR(2),
@.V_Length VARCHAR(10),
@.v_DisMonth VARCHAR(2)
-- Get Month
SELECT @.v_Month =Cast(DatePart(m,GETDATE()) as varchar)-- Feb
-- See Month
SELECT @.v_Month-- before conversion
-- Get Length of Month Return
SELECT @.v_Length = DATALENGTH(@.v_Month)
-- Make two digits if needed
SELECT @.v_DisMonth = CASE WHEN @.v_Length = 1
THEN '0'+@.v_Month
ELSE @.v_Month
END
-- See Month Correctly
SELECT @.v_DisMonth -- After Conversion
"SteveInBeloit" wrote:

> Hi,
> I am pulling the month from a date like this:
> Cast(DatePart(m,[bhDate]) as varchar)
> if it comes back 12, I get 12, if it comes back as the 7th month, I want t
o
> get '07'.
> I normally use the FORMAT function in VBA, but having trouble finding how
to
> do it in T-SQL
> Any thoughts,
> Steve
>|||For some reason, Microsoft hasn't implemented a generic string format
function in T-SQL.
To get 2-digit month, you can also use convert() function and let Microsoft
truncates for you.
convert(char(2), getdate(), 101)
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:A68D4075-7FB0-49B9-B598-7CF06485B21C@.microsoft.com...
> Try this REPLICATE('0',2-LEN(Cast(DatePart(m,[bhDate]) as varchar)))+
> Cast(DatePart(m,[bhDate]) as varchar)
> --
> Thanks & Rate the Postings.
> -Ravi-
>
> "SteveInBeloit" wrote:
>

format 0.00 decimal value

Is there any option/settings in SQL server to return 0 decimal value as
"0.00"
not
".00"
I can do it by converting all results to varchar(), but is there any easy
way? may be server settings?
thanks
L
Formatting of decimal data is done by the front end, not the server.
So the problem is with Query Analyzer, or whatever tool you are using.
If it is QA there is no feature like you are asking for.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 13:43:01 -0800, LLT
<LLT@.discussions.microsoft.com> wrote:

>Is there any option/settings in SQL server to return 0 decimal value as
>"0.00"
>not
>".00"
>I can do it by converting all results to varchar(), but is there any easy
>way? may be server settings?
>thanks
>L
|||I am using QA. The same in DTS when I extract data into txt file. Strange,
but float is returned with leading 0 as "0.00", decimal and money without as
".00". When number converted to string using cast() or str() leading 0 is
shown for all numbers.
You can run in in QA.
select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10))
select 'money, no varchar cast', cast(0.00 as money)
select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2))
as varchar(10))
select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))
select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))
select 'float, no varchar cast', cast(0.00 as float)
"Roy Harvey" wrote:

> Formatting of decimal data is done by the front end, not the server.
> So the problem is with Query Analyzer, or whatever tool you are using.
> If it is QA there is no feature like you are asking for.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 13:43:01 -0800, LLT
> <LLT@.discussions.microsoft.com> wrote:
>

format 0.00 decimal value

Is there any option/settings in SQL server to return 0 decimal value as
"0.00"
not
".00"
I can do it by converting all results to varchar(), but is there any easy
way? may be server settings?
thanks
LFormatting of decimal data is done by the front end, not the server.
So the problem is with Query Analyzer, or whatever tool you are using.
If it is QA there is no feature like you are asking for.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 13:43:01 -0800, LLT
<LLT@.discussions.microsoft.com> wrote:

>Is there any option/settings in SQL server to return 0 decimal value as
>"0.00"
>not
>".00"
>I can do it by converting all results to varchar(), but is there any easy
>way? may be server settings?
>thanks
>L|||I am using QA. The same in DTS when I extract data into txt file. Strange,
but float is returned with leading 0 as "0.00", decimal and money without as
".00". When number converted to string using cast() or str() leading 0 is
shown for all numbers.
You can run in in QA.
select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10)
)
select 'money, no varchar cast', cast(0.00 as money)
select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2))
as varchar(10))
select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))
select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))
select 'float, no varchar cast', cast(0.00 as float)
"Roy Harvey" wrote:

> Formatting of decimal data is done by the front end, not the server.
> So the problem is with Query Analyzer, or whatever tool you are using.
> If it is QA there is no feature like you are asking for.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 13:43:01 -0800, LLT
> <LLT@.discussions.microsoft.com> wrote:
>
>

Format

I have a number in a table. By example: 87
I need change this a varchar but with format, by example '00000087'
I need to make this change in a procedure...
Please help me!!!!
In oracle I used TO_CHAR!!!!!!! It was easy...
Thanks...This is best done in the client application (I just had to say it).
If you need it in T-SQL, then there are several solutions. One of them
is this:
Declare @.n int
Set @.n=87
SELECT Replicate('0',8-LEN(CAST(@.n AS varchar(11))))+CAST(@.n AS
varchar(11))
Another is this:
Declare @.n int
Set @.n=87
SELECT Right('00000000'+CAST(@.n AS varchar(11)),8)
HTH,
Gert-Jan
Francisco wrote:
> I have a number in a table. By example: 87
> I need change this a varchar but with format, by example '00000087'
> I need to make this change in a procedure...
> Please help me!!!!
> In oracle I used TO_CHAR!!!!!!! It was easy...
> Thanks...|||Thanks.
Now, My question is I am using Primary Keys like '00000000' .
In my procedure I need add one for the next id.
What is the best option for SQL Server? CHAR o INT?
Thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
news:4310D9B4.388ED320@.toomuchspamalready.nl...
> This is best done in the client application (I just had to say it).
> If you need it in T-SQL, then there are several solutions. One of them
> is this:
> Declare @.n int
> Set @.n=87
> SELECT Replicate('0',8-LEN(CAST(@.n AS varchar(11))))+CAST(@.n AS
> varchar(11))
> Another is this:
> Declare @.n int
> Set @.n=87
> SELECT Right('00000000'+CAST(@.n AS varchar(11)),8)
> HTH,
> Gert-Jan
>
> Francisco wrote:|||Here is an example of generating your own pk (using some undoc/unsupported
trick).
-- dynamic pk gen
use tempdb
go
create table seed(i int)
insert seed values(0)
go
create proc getval
as
begin
set nocount on
declare @.i int
update seed
set @.i=i=i+1
select convert(char(4),getdate(),12)+right(1000
000+@.i,6) as [i]
end
go
create function dbo.pkgen()
returns char(10)
as
begin
return(select i from openquery(your_server_name,'exec
tempdb..getval;commit')x)
end
go
create table t(pk char(10) primary key default dbo.pkgen(),i int)
go
insert t(i) values(10)
insert t(i) values(20)
insert t(i) values(30)
select * from t
go
go
drop table t
drop function dbo.pkgen
drop proc getval
drop table seed
-oj
"Francisco" <fvicente@.terra.com> wrote in message
news:OtqjrJ1qFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Thanks.
> Now, My question is I am using Primary Keys like '00000000' .
> In my procedure I need add one for the next id.
> What is the best option for SQL Server? CHAR o INT?
> Thanks.
>
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
> news:4310D9B4.388ED320@.toomuchspamalready.nl...
>|||I would only consider two options:
1) If you want the system to automatically generate the key (a surrogate
key), then you could simply use an Identity column (int)
... MyKey int not null IDENTITY PRIMARY KEY
2) If you want a natural key or at least have some influence and
repeatability in assigning the key, then choose whatever format you
like. Have you client (application) assign the key. If you are planning
on using a number with leading zeros, and it is always fixed length,
then there is no use to start messing with formatting functions. You can
simply store it in a character column, and enforce the format with a
constraint
... MyKey char(8) not null PRIMARY KEY
, CONSTRAINT CK_MyTable_MyKeyFormat
CHECK ( Len(MyKey)=8
AND MyKey NOT LIKE '%[^0-9]%' )
Although the code that oj posted is very interesting and all, I could
not recommend this unnecessary complexity.
HTH,
Gert-Jan
Francisco wrote:
> Thanks.
> Now, My question is I am using Primary Keys like '00000000' .
> In my procedure I need add one for the next id.
> What is the best option for SQL Server? CHAR o INT?
> Thanks.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
> news:4310D9B4.388ED320@.toomuchspamalready.nl...