0

This is actually a follow-up question to a topic mentioned here: split alpha and numeric using sql Facing similar problems, I adjusted @knkarthick24 (a chance to say tnx!) query to my needs almost perfectly (I have “2,500” not “2 500”) as follow:

SELECT [Quantity]
    ,substring(replace(subsrtunit, ',', ''), PATINDEX('%[0-9.]%', replace(subsrtunit, ',', '')) + 1, len(subsrtunit)) AS unit
    ,LEFT(replace(subsrtnumeric, ',', ''), PATINDEX('%[^0-9.]%', replace(subsrtnumeric, ',', '') + 't') - 1) AS num
FROM (
    SELECT [Quantity]
        ,subsrtunit = SUBSTRING([Quantity], posofchar, LEN([Quantity]))
        ,subsrtnumeric = SUBSTRING([Quantity], posofnumber, LEN([Quantity]))
    FROM (
        SELECT [Quantity]
            ,posofchar = PATINDEX('%[^0-9.]%', replace([Quantity], ',', ''))
            ,posofnumber = PATINDEX('%[0-9.]%', replace([Quantity], ',', ''))
        FROM [OPI].[dbo].[MRRInvoices]
        WHERE DocumentNum IS NOT NULL
        ) d
    ) t

the only thing left is handling negative values.

Right now the results I'm getting for a negative value in Quantity field (-1.00 GB) is (.00 GB) in the unit field and (1.00) for num field.

Also, does anyone know how to "translate" it to derived column in SSIS? Can "Findstring" in SSIS replace PATINDEX?

Thank you all in advance.

!1[img]

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yaeli Hajbi
  • 1
  • 1
  • The problem is that the measurement and units were stored as text in the same field - never mind mixing concentrations and percentages. That's a fundamental design error. You should *fix* the bad design otherwise you'll *always* have parsing problems, aggregation problems, invalid results because 90% was counted as 90. – Panagiotis Kanavos Jan 24 '18 at 12:27
  • You can use SSIS and a script component when *loading* the data to parse the field and extract the components to separate columns, *and* normalize them to ensure they have the same scale. If you want to store μg/mL, all values should be scaled to that. Otherwise you'll have trouble aggregating – Panagiotis Kanavos Jan 24 '18 at 12:28
  • Tnx. SSIS script is a great idea since my source files are untouchable (not made by me and I don't have any 'say' in the matter.) But can you suggest how to solve the negative values issue? – Yaeli Hajbi Jan 24 '18 at 13:13

0 Answers0