"Wavemachine" <fred@nospam.com> wrote in message
news:ehK45RsIEHA.1140@tk2msftngp13.phx.gbl...
> Hi,
>
> I have to convert a field from a VARCHAR to a float on existing client
> databases.
>
> I am using IsNumeric to determine whether the column is a number and if it
> is using CONVERT to change it to a float.
>
> However IsNumeric seems to allow certain characters such as the $ sign and
> commas and the convert the throws an error.
>
> Basically I need a function to either remove any non numeric characters or
> to say it is not numeric if it contains any of these characters.
>
> I have had a look at writing a UDF but this seems to be along similar
lines
> to an extended stored proc which is overkill for a one off conversion so
> guess a stored proc of some kind would be the solution.
>
> Any ideas please
>
> Thanks
>
> Dave
>
>

"Bern Taylor" <thebg@btinternet.com> wrote in message
news:%23WddlFwIEHA.3964@TK2MSFTNGP10.phx.gbl...
> You could use the equivalent of
>
> declare @p1 float
> select @p1=convert(money,'$1,432.123')
>
> Bern
>
> "Wavemachine" <fred@nospam.com> wrote in message
> news:ehK45RsIEHA.1140@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I have to convert a field from a VARCHAR to a float on existing client
> > databases.
> >
> > I am using IsNumeric to determine whether the column is a number and if
it
> > is using CONVERT to change it to a float.
> >
> > However IsNumeric seems to allow certain characters such as the $ sign
and
> > commas and the convert the throws an error.
> >
> > Basically I need a function to either remove any non numeric characters
or
> > to say it is not numeric if it contains any of these characters.
> >
> > I have had a look at writing a UDF but this seems to be along similar
> lines
> > to an extended stored proc which is overkill for a one off conversion so
> > guess a stored proc of some kind would be the solution.
> >
> > Any ideas please
> >
> > Thanks
> >
> > Dave
> >
> >
>
>