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 2012 Forums
 SQL Server Administration (2012)
 val

Author  Topic 

rssrk
Starting Member

9 Posts

Posted - 2013-08-21 : 05:17:27
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

30421 Posts

Posted - 2013-08-21 : 06:51:34
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

9 Posts

Posted - 2013-08-21 : 07:40:10
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 07:50:57
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

9 Posts

Posted - 2013-08-21 : 08:57:35
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

9 Posts

Posted - 2013-08-21 : 09:49:34
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-22 : 03:18:36
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
   

- Advertisement -