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 2012 Forums
 SQL Server Administration (2012)
 val
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rssrk
Starting Member

India
9 Posts

Posted - 08/21/2013 :  05:17:27  Show Profile  Reply with Quote
Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to float
this error is coming when i m using '\' backslash in
select dbo.val('\123rahul')
how to overcome it

SwePeso
Patron Saint of Lost Yaks

Sweden
30213 Posts

Posted - 08/21/2013 :  06:51:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How can we tell without seeing your existing code?
Post the content of function dbo.Val and we can give some advice and even solve your dilemma.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

rssrk
Starting Member

India
9 Posts

Posted - 08/21/2013 :  07:40:10  Show Profile  Reply with Quote
code-
create function Val
(
@text nvarchar(140)
)
returns float
as begin

if @text is null
begin
return 0
end


-- emulate vba's val() function
declare @result float
declare @tmp varchar(40)

set @tmp = @text
while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
set @tmp=left(@tmp,len(@tmp)-1)
end
set @result = cast(@tmp as float)

return @result
end
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/21/2013 :  07:50:57  Show Profile  Reply with Quote
quote:
Originally posted by rssrk

code-
declare @result float
declare @tmp varchar(40)

set @tmp = REPLACE(@text+'\', '\', '') -- Replace \ with empty string
while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
set @tmp=left(@tmp,len(@tmp)-1)
end
set @result = cast(@tmp as float)

return @result
end



set @tmp=REPLACE(@tmp+'\', '\', '')

--
Chandu
Go to Top of Page

rssrk
Starting Member

India
9 Posts

Posted - 08/21/2013 :  08:57:35  Show Profile  Reply with Quote
its returning null value after your modification....
please mention the whole code and how to replace\ with empty string ??
Go to Top of Page

rssrk
Starting Member

India
9 Posts

Posted - 08/21/2013 :  09:49:34  Show Profile  Reply with Quote
sorry....its working
now its returning correct value and not giving error
thanks a lot, really it helped me a lot thank you very much
stay blessed tc
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/22/2013 :  03:18:36  Show Profile  Reply with Quote
quote:
Originally posted by rssrk

sorry....its working
now its returning correct value and not giving error
thanks a lot, really it helped me a lot thank you very much
stay blessed tc


welcome

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000