-1

How do I make it a number instead of varchar?

(SELECT CAST(CAST((
    SELECT SUBSTRING(r.result, Number, 1)
    FROM master..spt_values
    WHERE Type='p' AND Number <= LEN(r.result) AND
        SUBSTRING(r.result, Number, 1) LIKE '[0-9\.]'FOR XML Path(''))
AS xml) AS varchar(MAX)))

I tried

(SELECT CAST(CAST(CAST((
    SELECT SUBSTRING(r.result, Number, 1)
    FROM master..spt_values
    WHERE Type='p' AND Number <= LEN(r.result) AND
        SUBSTRING(r.result, Number, 1) LIKE '[0-9\.]'FOR XML Path(''))
AS xml) AS varchar(MAX)) as numeric)) AS [ResNumber]

but I get Arithmetic overflow error converting varchar to data type numeric.

r.result without the cast statement returns data something like this

r.result
<452Con
12
0.0
>10
14
1.00300

or alternatively How do I say

IIF ---> (SELECT CAST(CAST(CAST(( SELECT SUBSTRING(r.result, Number, 1) FROM master..spt_values WHERE Type='p' AND Number <= LEN(r.result) AND SUBSTRING(r.result, Number, 1) LIKE '[0-9.]'FOR XML Path('')) AS xml) AS varchar(MAX)) as decimal(18,6)) AS [ResNumber] ---> is greater than 10 then return 1 if its not return 0?

Tami
  • 17
  • 6
  • Table alias `r` refers to what? (Does the Universal Pirate Table have a `result` column?) Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. – HABO Aug 23 '23 at 17:07
  • @HABO r.result r is the alias for my results table in the db. result is the results of a medical test. it is a string that may have symbols, letters, and numbers in it. I can pull out the numbers with the decimal point with this, but it won't convert that to a numerical value that I can use further calculations on such as divide by 2 etc too much PHI to include the whole search in here. – Tami Aug 23 '23 at 17:31
  • some sample data would help along with the expected results based on that sample data, as it stands we have no idea what is being returned in r.result – Alan Schofield Aug 23 '23 at 17:49
  • @AlanSchofield sorry I added what r.result would return raw without the cast statement. its a varchar 'string' really bad data word salad – Tami Aug 23 '23 at 18:00
  • Your code reads something like "Traipse through an assortment of numbers chosen from an undocumented/unsupported system table and use them to pull individual characters from an unspecified string in no particular order. If the characters happen to be digits or periods then assemble them into a string. Take the resulting string and make it a `numeric(18,0)` value." Did you try something like [this test](https://dbfiddle.uk/azbR4FIy) to see what is going on. Aside: This is a _statement_: "Functions and expressions are **NOT** _statements_." – HABO Aug 23 '23 at 19:40
  • I've edited your question but may not have understood what you were trying to show so edit it if this is incorrect, I assumed you showed several rows, not a single row with line feeds. Can you also show the expect results, it might be obvious but it help clarify what you are trying to do. – Alan Schofield Aug 23 '23 at 19:42
  • @AlanSchofield I have data like in the table. Its full of junk without a pattern. I need to strip out everything but the number portion including the decimal. So I can do further calculations on it such as of >= .3 or <10 I keep getting conversion errors. It can't do a greaterthan/lessthan on anthing but a true number it seems. The junk column is varchar. – Tami Aug 28 '23 at 20:29

0 Answers0