Clear Thinking in Data, Cloud, and AI

I have this paragraph;

Speeding ticket is 210.99USD. Aggravated DUI could add up 1 year jail time.

This is a standard text where the pattern is like this;

Speeding ticket is [Amount]. Aggravated DUI could add up [Term] year jail time.

The ask is to extract Amount and Term from this text. The approach is to split the paragraph and use SQL IsNumeric functions to extract the values;

Here is a sample prototype;

DECLARE @ParagraphText NVARCHAR(MAX) = N'Speeding ticket is 210.99USD. Aggrevated DUI could add up 1 year jail time.'

--table variable
DECLARE @Test TABLE (ValueColumn VARCHAR(8000))
INSERT @Test
--I am using a custom function but you can use string_split() on SQL 2017 and upward
SELECT * FROM dbo.fnSplitString(@ParagraphText, ' ')

--using windows ranking function to get both values
SELECT ROW_NUMBER() OVER(ORDER BY ValueColumn) [ROW_NUMBER],*
FROM
(
    SELECT
    CONVERT(DECIMAL(20,8),
    CASE 
	WHEN IsNumeric(ValueColumn)=1 THEN 	CONVERT(FLOAT,ValueColumn)
	ELSE CONVERT(FLOAT,'0'+LEFT(ValueColumn,PATINDEX('%[^0-9.]%',ValueColumn)-1))
    END) AS ExtractedColumn
    ,ValueColumn
    FROM @Test
) x
WHERE x.ExtractedColumn > 0

Add to favorites