val - SQL Server Forums

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

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources