Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to convert nvarchar datatype to float
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/16/2008 :  08:53:33  Show Profile  Reply with Quote
Hi,

how to convert nvarchar datatype to float?

Regards
Prashant

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/16/2008 :  08:59:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Did you even try to find out yourself?

http://www.google.com/search?q=convert+nvarchar+float

Is it really quicker and easier to post a question in a forum and wait for someone to help you rather than to do a simple Google search?

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 05/16/2008 09:00:17
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/16/2008 :  09:08:35  Show Profile  Reply with Quote
Thanks for the reply....

I have tried my best bt didn't find any way to do it...

Please see the below procedure...

USE [TradeCapture]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

GO
IF (EXISTS (SELECT name FROM SYS.sysobjects
WHERE (name = N'Proc_Submit_PITS_Order_List')
AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN'))))
DROP PROCEDURE [dbo].[Proc_Submit_PITS_Order_List]
GO

-- =================================================================================================================
-- Author: Prashant Hirani
-- Create date: May 13, 2008
-- =================================================================================================================
-- EXEC Proc_Submit_PITS_Order_List ' 0,0,0,0, 55.55, 20, 'B'' , 0,0 ,0, 60.00, 15, 'B'

CREATE PROCEDURE [dbo].[Proc_Submit_PITS_Order_List]
@P_PitId nvarchar(4000),
@P_OrderId nvarchar(4000),
--@P_OrderDate DATETIME,
@P_Cust_id INT,
@P_Trader_Id INT,
@P_Price nvarchar(4000),
@P_Size nvarchar(4000),
@P_Buysell NVARCHAR(10)
--@p_TradeVision_List nvarchar(4000)

AS
BEGIN
-- =================================================================================================================
SET NOCOUNT ON

--DECLARE @P_TRADEVISION_ID INT
DECLARE @p_Cnt_Duplicate_Rec INT
DECLARE @p_Max_Version INT
DECLARE @p_Status NVARCHAR(50)

DECLARE @tbl table
(
PitId int,
OrderId int,
Price Float,
[Size] Float
)

DECLARE @p_Tmp_Str1 nvarchar(4000)
DECLARE @p_Tmp_Str2 nvarchar(4000)
DECLARE @p_Tmp_Str3 nvarchar(4000)
DECLARE @p_Tmp_Str4 nvarchar(4000)

Set @p_Tmp_Str1 = NULL
Set @p_Tmp_Str2 = NULL
Set @p_Tmp_Str3 = NULL
Set @p_Tmp_Str4 = NULL

While IsNull(Len(@P_PitId),0) > 0
BEGIN
IF IsNull(Len(@P_PitId),0) <= 0 BREAK

If IsNull(charindex(',',@P_PitId),0) > 0
Set @p_Tmp_Str1 = (select left(@P_PitId,IsNull(charindex(',',@P_PitId) - 1,0)))
ELSE
Set @p_Tmp_Str1 = @P_PitId

If IsNull(charindex(',',@P_OrderId),0) > 0
Set @p_Tmp_Str2 = (select left(@P_OrderId,IsNull(charindex(',',@P_OrderId) - 1,0)))
ELSE
Set @p_Tmp_Str2 = @P_OrderId

If IsNull(charindex(',',@P_Price),0) > 0
Set @p_Tmp_Str3 = (select left(@P_Price,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str3 = @P_Price

If IsNull(charindex(',',@P_Size),0) > 0
Set @p_Tmp_Str4 = (select left(@P_Size,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str4 = @P_Size

If (IsNumeric(@p_Tmp_Str1)= 1) AND
(IsNumeric(@p_Tmp_Str2)= 1) AND
(IsNumeric(@p_Tmp_Str3)= 1) AND
(IsNumeric(@p_Tmp_Str4))= 1
Begin
Insert Into @tbl
Values( Convert(INT, @p_Tmp_Str1), Convert(INT, @p_Tmp_Str2), Convert(float, @p_Tmp_Str3), Convert(float, @p_Tmp_Str4))
END

------ --------------
If IsNull(Len(@P_PitId),0) > IsNull(Len(@p_Tmp_Str1),0)
Set @P_PitId = (select right(@P_PitId, IsNull(Len(@P_PitId),0) - IsNull(Len(@p_Tmp_Str1) + 1,0)))
Else
Set @P_PitId = null
--------------------
If IsNull(Len(@P_OrderId),0) > IsNull(Len(@p_Tmp_Str2),0)
Set @P_OrderId = (select right(@P_OrderId, IsNull(Len(@P_OrderId),0) - IsNull(Len(@p_Tmp_Str2) + 1,0)))
Else
Set @P_OrderId = null
--------------------
If IsNull(Len(@P_Price),0) > IsNull(Len(@p_Tmp_Str3),0)
Set @P_Price = (select right(@P_Price, IsNull(Len(@P_Price),0) - IsNull(Len(@p_Tmp_Str3) + 1,0)))
Else
Set @P_Price = null
--------------------
If IsNull(Len(@P_Size),0) > IsNull(Len(@p_Tmp_Str4),0)
Set @P_Size = (select right(@P_Size, IsNull(Len(@P_Size),0) - IsNull(Len(@p_Tmp_Str4) + 1,0)))
Else
Set @P_Size = null

END

SELECT * FROM @tbl

-----------------------------------------------------

Try to execute with the below parameter
EXEC Proc_Submit_PITS_Order_List '116661,116662,166610', '154443,144454, 154448', 0, 0, '5555, 6546, 7457', '5558, 5659, 9599', 'B'

Before knowing from the opposite site never give advice to anyone....

Thanks
Prashant Hirani

Edited by - hirani_prashant on 05/16/2008 09:12:48
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/16/2008 :  10:44:54  Show Profile  Reply with Quote
What was the problem when you used convert? DId you get any error?
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/17/2008 :  01:06:19  Show Profile  Reply with Quote

Ya, it gives me error like "Conversion failed when converting the nvarchar value to float"

Thanks
Prashant
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/17/2008 :  03:32:47  Show Profile  Reply with Quote
Now let me explain with the simple terminology...

Mine Below code works fine...

declare @v1 as nvarchar
set @v1='99922.6'

select cast(@v1 as int)
select cast(@v1 as money)

SELECT CONVERT(int, @v1)
SELECT CONVERT(money, @v1)


Bt at the same time mine below code gives me error...

declare @v1 as nvarchar(4000)
set @v1='99922.6'

select cast(@v1 as int)
select cast(@v1 as money)

SELECT CONVERT(int, @v1)
SELECT CONVERT(money, @v1)

Can any one help me out to solve this problem???

Thanks
Prashant
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/17/2008 :  04:47:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
do a PRINT @v1

and you will spot the error...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ganeshkumar08
Posting Yak Master

India
187 Posts

Posted - 05/17/2008 :  05:30:27  Show Profile  Send ganeshkumar08 an AOL message  Reply with Quote
declare @v1 as nvarchar(4000)
set @v1='99922.6'

select cast(@v1 as float)

The above is working in converting nvarchar to float, then wats your problem..
Go to Top of Page

chandan_joshi80
Starting Member

India
30 Posts

Posted - 05/17/2008 :  08:54:23  Show Profile  Reply with Quote

DECLARE @V1 NVARCHAR(20)
SET @V1='232'
PRINT @V1
SELECT CAST(@V1 AS NUMERIC(18,2))

chandan Joshi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/17/2008 :  13:56:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And Chandan wins the cheese.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 05/19/2008 :  01:22:09  Show Profile  Reply with Quote
It was my mistake inside the below if loop.

There i mention the wrong parameter "@P_Size". In Actual i have mention "@P_Price" Parameter...

If IsNull(charindex(',',@P_Price),0) > 0
Set @p_Tmp_Str3 = (select left(@P_Price,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str3 = @P_Price

Any ways thanks for your valuable response.

Thanks
Prashant Hirani
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000