T-SQL - column string

Question

Apologies I have searched for a question to my answer to no avail.

I have the following column which has the following narrative

Narrative

• The budget for this is 68000 GBP
• Product C will cost GBP 300000
• Product B will cost -6650 EUR

I've managed to create a currency column with a case statement and now I'm trying to create a column with just the numeric values and removing all text except for the numeric values.

I want to go from having

Narrative
---------------------------------
The budget for this is 68000 GBP
Product C will cost GBP 300000
Product B will cost -6650 EUR

to

Values      Currency
---------------------
 68000      GBP
300000      GBP
(650)       EUR

I'm just wondering if there is a quick way of doing this with T-SQL. There isn't a delimiter in the narrative column I can use but the numeric values are always the last text in the string.

Many thanks

Apologies I've not got this to work maybe being novice I was unable to explain but here is my code

~SELECT SYSAccountingPeriodID, GoodsValueInBaseCurrency, TransactionDate, Reference, Narrative, UserName, CASE WHEN Narrative LIKE '%USD%' THEN 'USD' WHEN Narrative LIKE '%GBP%' THEN 'GBP' WHEN Narrative LIKE '%EUR%' THEN 'EUR' ELSE 'EUR' END AS Currency FROM dbo.NLPostedNominalTran~

Here is the output

Answer 1

Assuming that the last 2 "words" in Narrative are the price and currency, you could do something like the below. This is messier than it should be, due to the inconsistency of the data (Price Currency and Currency Price both appear) and that the - character isn't part of the price value.

SELECT TRY_CONVERT(decimal(10,2),CASE WHEN LS.FirstWord LIKE '%[^0-9]%' THEN LS.SecondWord ELSE LS.FirstWord END) * IIF(LR.Minus = '-',-1,1) AS Price,
       CASE WHEN LS.FirstWord LIKE '%[^0-9]%' THEN LS.FirstWord ELSE LS.SecondWord END AS Currency
FROM (VALUES('The budget for this is 68000 GBP'),
            ('Product C will cost GBP 300000'),
            ('Product B will cost - 6650 EUR'))V(Narrative)
     CROSS APPLY (VALUES(CHARINDEX(' ',REVERSE(V.Narrative))))CI1(I)
     CROSS APPLY (VALUES(CHARINDEX(' ',REVERSE(V.Narrative),CI1.I+1)))CI2(I)
     CROSS APPLY (VALUES(RIGHT(V.Narrative,CI2.I-1)))R(Price)
     CROSS APPLY (VALUES(LEFT(R.Price,CHARINDEX(' ',R.Price)-1),STUFF(R.Price,1,CHARINDEX(' ',R.Price),'')))LS(FirstWord,SecondWord)
     CROSS APPLY (VALUES(LEFT(RIGHT(V.Narrative,CI2.I+1),1)))LR(Minus)

Notice I convert the value to a decimal in the SELECT too. If you can only have integer values, then you can use an int there.

In your actual expected results you have '(650)'. I assume 650 is a typographical error, as the sample data has 6650. As for the value being in parenthesis (()) to denote it is a negative number, that should be done in the presentation layer; therefore in the data engine it will appear as -6650.00 (assuming a decimal(10,2)).

Answer 2

If you are using SQL server 2016 or later, then you have the string_split function at your disposal. Combine it with a common table expression (CTE), some apply's and case statements and you can build the following solution.

Your sample data has a space between the minus sign and the value. If this is not always the case in your entire data set, then the approach might require some tweaking, but the general solution path can remain.

1. Split the string into values based on spaces
2. Filter out numeric values (includes minus signs) and currencies
3. Start from numeric values and append currency, use case to format negative values

Sample data

declare @data table
(
    narative nvarchar(50)
);

insert into @data (narative) values
('The budget for this is 68000 GBP'),
('Product C will cost GBP 300000'),
('Product B will cost - 650 EUR');

Solution

with cte as
(
    select  d.narative,
            s.value,
            case when s.value = '-' then 1 else 0 end as 'IsNeg',
            isnumeric(s.value) as 'IsNum'
          --case when try_convert(decimal(10,2), s.value) is not null then 1 else 0 end as 'IsNum2'
    from @data d
    cross apply string_split(d.narative, ' ') s
    where isnumeric(s.value) = 1
       or s.value in ('EUR', 'GBP')
)
select  c1.narative,
        case when c4.IsNeg = 1 then '(' else '' end
        + c1.value
        + case when c4.IsNeg = 1 then ') ' else ' ' end
        + c3.value as 'money'
from cte c1
cross apply (   select c2.value
                from cte c2
                where c2.narative = c1.narative
                  and c2.IsNum = 0 ) c3
outer apply (   select c2.IsNeg
                from cte c2
                where c2.narative = c1.narative
                  and c2.IsNeg = 1 ) c4
where c1.IsNum = 1
  and c1.IsNeg = 0;

Result

-- cte

narative                            value     IsNeg       IsNum
----------------------------------- --------- ----------- -----------
The budget for this is 68000 GBP    68000     0           1
The budget for this is 68000 GBP    GBP       0           0
Product C will cost GBP 300000      GBP       0           0
Product C will cost GBP 300000      300000    0           1
Product B will cost - 650 EUR       -         1           1
Product B will cost - 650 EUR       650       0           1
Product B will cost - 650 EUR       EUR       0           0


-- final

narative                            money
----------------------------------- -------------
The budget for this is 68000 GBP    68000 GBP
Product C will cost GBP 300000      300000 GBP
Product B will cost - 650 EUR       (650) EUR