How to get rid of the zeros in money type, Thanks!

How to get rid of the zeros in money type,

i.e. only show dollars, no cents?

I use:

CONVERT(varchar, Price, 1) AS Price

It gives me the result with 2 digits decimals. like 123,456.00

I only need the dollar part, how to get rid of the zeros, Thanks!

CONVERT(int, a) AS Price

CONVERT(numeric, a) AS Price

Also take a look at the following for CAST and CONVERT

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

|||

Consider the functions CEILING() and FLOOR().

Zlatko

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:

> I am using SQL Server 2000 and in SP or Function, my problem is as follows
:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of co
de
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:
> I am using SQL Server 2000 and in SP or Function, my problem is as follows:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of code
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

How to get exact matches & better ranked results using freetexttable?

I have a SQL Server 2000 table with a varchar column (these are part
numbers) that contains data like this:
145
145-SC
145-AB
145-2
145-8
The full text catalog also includes columns that hold part name and
part description. I'm using the following query to return results for
product searches:
select ftt.RANK,
products.ref,
products.partno,
products.partname,
products.refparent
from PRODUCTS
INNER JOIN
FREETEXTTABLE(products,*,'145-8') as ftt
ON
ftt.[KEY]=products.ref
order by ftt.rank desc
My results are:
rankrefpartnoname
278385414Aqua kit
278385514-SCDAqua kit
278385614-SCZAqua kit
278385714-UGAqua kit
183386819Standard T&L Kit
183386919-SCDStandard T&L Kit
183387019-SCZStandard T&L Kit
183387119-UBStandard T&L Kit
754121145BADGE, 7.00 X 7.00
754122145-8BADGE, 7.00 X 7.00
754123145-25BADGE, 7.00 X 7.00
754124145-SCDBADGE, 7.00 X 7.00
754125145-SCZBADGE, 7.00 X 7.00
754126145-UBBADGE, 7.00 X 7.00
How can I improve my query to return 145-8 (in this instance) as the
highest ranking result?
Thanks for your help!
Its hard to tell from looking at your data what data belongs in what
columns, for example in this row
rank ref partno name
75 4126 145-UB BADGE, 7.00 X 7.00
is rank=75, ref=4126, partno=145-UB BADGE and name= 7.00 X 7.00
or is it something else like this
rank=75, ref=4126, partno=145-UB and name=BADGE, 7.00 X 7.00
Did you remove all of the letters and number from your noise word list?
Also perhaps you should just search on partno.
Also I think contains will give you what you are looking for, but its hard
to tell as I don't really understand what your data looks like.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jasolution@.gmail.com> wrote in message
news:1168698214.665262.131510@.m58g2000cwm.googlegr oups.com...
>I have a SQL Server 2000 table with a varchar column (these are part
> numbers) that contains data like this:
> 145
> 145-SC
> 145-AB
> 145-2
> 145-8
> The full text catalog also includes columns that hold part name and
> part description. I'm using the following query to return results for
> product searches:
> select ftt.RANK,
> products.ref,
> products.partno,
> products.partname,
> products.refparent
> from PRODUCTS
> INNER JOIN
> FREETEXTTABLE(products,*,'145-8') as ftt
> ON
> ftt.[KEY]=products.ref
> order by ftt.rank desc
> My results are:
> rank ref partno name
> 278 3854 14 Aqua kit
> 278 3855 14-SCD Aqua kit
> 278 3856 14-SCZ Aqua kit
> 278 3857 14-UG Aqua kit
> 183 3868 19 Standard T&L Kit
> 183 3869 19-SCD Standard T&L Kit
> 183 3870 19-SCZ Standard T&L Kit
> 183 3871 19-UB Standard T&L Kit
> 75 4121 145 BADGE, 7.00 X 7.00
> 75 4122 145-8 BADGE, 7.00 X 7.00
> 75 4123 145-25 BADGE, 7.00 X 7.00
> 75 4124 145-SCD BADGE, 7.00 X 7.00
> 75 4125 145-SCZ BADGE, 7.00 X 7.00
> 75 4126 145-UB BADGE, 7.00 X 7.00
> How can I improve my query to return 145-8 (in this instance) as the
> highest ranking result?
> Thanks for your help!
>