开发者

I am unable to convert numbers extracted from a text field to be considered numeric in SQL

开发者 https://www.devze.com 2023-03-13 15:13 出处:网络
I have a field in our SQL DB, labeled result-answer. In this field are results and any notes appended to them. I found a logic to extract the numbers from the field, but I can\'t convert them to be nu

I have a field in our SQL DB, labeled result-answer. In this field are results and any notes appended to them. I found a logic to extract the numbers from the field, but I can't convert them to be numeric. We are using Precision BI as our intermediary program.

I am attempting to pull only those with a value less than 7. I have the numbers alone using a string that extracts only the numbers from the fields.

Left(SubString([result].[resultanswer], PatIndex('%[0-9.-]%', [result].
[resultanswer]), 8000), PatIndex('%[^0-9.-]%', SubString([result].[resultanswer],
PatIndex('%[0-9.-]%', [result].[resultanswer]), 8000) + 'X')-1)

However when I add the query to only display those numbers less than 7 I receive a conversion error, most frequently along the lines of 'unable to convert varchar to numeric.' If I run the search without the less than 7 query it will run just fine and only display numbers in this field. If I add a field to show if that field isnumeric it displays 1 or yes for all of them. I can take and convert the values to numeric through a convert function or a cast function (adding convert or cast to the beginning of the above string) , but as soon as I add the less than 7 criteria it says that it can't convert, even though, it did before adding the criteria. The whole thing doesn't make sense.

Here is a sample of the data.

result-numericresult    result-resultanswer result-numeric
9.23                    9.23                    9.23
.00                     9.24                    9.24
.00                     9.24 Result Repeated    9.24
.00                     9.25                    9.25
9.25                    9.25                    9.25

As you can see I have a result field (called numericresult) that has the numerics already, the problem with it is that the program supplying the data to the DB is sending zeros instead of actual valu开发者_JAVA技巧es. I also have a result answer field (called resultanswer), which gives me the numerics, but also has text. The goal was to basically replace the numericresult field with a real numericresult field that doesn't have zeros when it shouldn't. Thus the string to pull the numbers from the resultanswer field. The last column (called numeric) is the custom field that pulls the numbers from the resultanswer field and is the field I am attempting to measure by. So those values less than 7.

I apologize for not including this info in the original post, but we had a bad storm coming and the lights were fickering. So rather than lose what I had, I went ahead and posted.


CAST(value AS int)

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Or you could CAST as decimal(x,y) if you prefer.

Once you've made it numeric, you can perform numeric operations and comparisons on it.
Note though that if you CAST some non-numeric value the query will fail.

0

精彩评论

暂无评论...
验证码 换一张
取 消