how to convert nvarchar datatype to float

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to convert nvarchar datatype to float

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-16 : 08:53:33

Hi,

how to convert nvarchar datatype to float?

Regards
Prashant

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-16 : 08:59:26

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

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-16 : 09:08:35

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 10:44:54

What was the problem when you used convert? DId you get any error?

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-17 : 01:06:19

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

Thanks
Prashant

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-17 : 03:32:47

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-17 : 04:47:29

do a PRINT @v1

and you will spot the error...

E 12°55'05.25"
N 56°04'39.16"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-17 : 05:30:27

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..

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-05-17 : 08:54:23

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

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-17 : 13:56:43

And Chandan wins the cheese.

E 12°55'05.25"
N 56°04'39.16"

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-19 : 01:22:09

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources