conversion function ?

1. Senior Member
Join Date
Dec 1969
Posts
158

## conversion function ?

Do we have a function in TSQL that can convert a string into a number? similar to the VAL() function in VB like VAL(03) = 3? thanks.

2. Senior Member
Join Date
Dec 1969
Posts
185

## user function

you have to make a user defined function like this.<BR>this function returns varchar<BR>you may want to change to an int or whatever.<BR><BR><BR>CREATE function fn_val (@input varchar(255))<BR>returns varchar(255)<BR>as<BR>begin<BR><BR> -- usage example: select dbo.fn_val(&#039;9klasjdljkasd123&#039;)<BR> -- example output: 9123 (varchar)<BR><BR> declare @len int <BR> declare @pos int <BR> declare @temp varchar(255)<BR> declare @ch varchar(1)<BR><BR> set @pos = 1<BR> set @len = len(@input)<BR> set @temp = &#039;&#039;<BR> <BR> --loop and filter limit to numeric<BR> while @pos &#060;= @len<BR> begin<BR> <BR> set @ch = substring(@input, @pos, 1)<BR><BR> if (@ch &#062;= &#039;0&#039; and @ch &#060;= &#039;9&#039;)<BR> set @temp = @temp + @ch<BR><BR> set @pos = @pos + 1<BR><BR> end<BR><BR> return(@temp)<BR><BR>end<BR><BR><BR><BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Way too complex!

You&#039;d only need something like if the string was not already a valid number.<BR><BR>He said he wanted something similar to VB&#039;s VAL function, and that function wouldn&#039;t handle VAL("9klasjdljkasd123"), right?<BR><BR>So it&#039;s dirt simple:<BR><BR> Convert( VARCHAR, numericField )<BR>OR<BR> Cast( numericField AS VARCHAR )<BR><BR>Read:<BR>http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp<BR><BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
185

## cast and convert don't work

select cast(&#039;1qqwe2&#039; as int)<BR>Syntax error converting the varchar value &#039;1qqwe2&#039; to a column of data type int.<BR><BR>here&#039;s what vb val does<BR>x = val(&#039;1qqwe2&#039;)<BR>&#039;x is now 1<BR>x = val(&#039;qqwe2&#039;)<BR>&#039;x is now 0<BR><BR><BR><BR>My function was wrong too though. The loop should stop after the first non-number and if nothing is returned it should return 0

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## That's what I said...

I said that if the string isn&#039;t a valid number then he needs a function like yours.<BR><BR>But what he *asked* for was something the equivalent of VB&#039;s VAL( ) function, and that&#039;s exactly what cast/convert do.<BR><BR>I certainly wasn&#039;t saying your code was wrong! Just that *if* he really only needed a VAL() replacement, there was a simpler way.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•