Search code examples
sql-servert-sql

Extract numeric values out of a nvarchar column in SQL

I'm trying to extract the number of gallons out of a string column in SQL. However, I'm not quite sure how to do so since the string column can contain anything. Attached is an image of what I'm dealing with. I managed to write a query that removes everything before the first numerical character (SQL below and the "Test" column in the image) however, can't figure out how to remove everything after the numerical characters in the "Test" column. There's also a scenario where there isn't a numerical value at all in the column, and a date field which I also am not concerned about.

SQL statement removing everything to the left of first numerical number:

substring([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])) as Test

The results I'm trying to get:

null
29.10
129
null
140
382
112.50
250
69.60
135.4

Example

enter image description here

Solution

  • First, I grabbed everything before the first numerical character in this string field. Query:

    substring([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])) as Test

    Since the numerical values were now all on the left, I then used a charindex in a case statement to remove everything to the right after the space. I used a case statement because some columns didn't have a space so I was getting an error. Query:

    ,SUBSTRING(Test, 1 ,
case when  CHARINDEX(' ', Test) = 0 then LEN(Test) 
else CHARINDEX(' ', Test) -1 end) as GallonAmount

    Result