SQL Server Forums
Profile | Register | 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
 New Topic  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
52249 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
29910 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
29910 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000