Topsy turvy in SQL

Transact-SQL
1

Hello Techie,

i came across very unique situation

I need to write sql code which perform Division on column i.e (Per Car Quantity = Cylinders/Description)

Per Car Quantity contain some different value other than the calculation, then those records

be in output.

description quantity should be the very last string seperated by semicolon

but the real problem are data in column

  1. data in description given as OE Replacement; seven-- 7 in words sometime
    and sometime in numeric form
  2. if data in two column are not even something like 15/7 then i need to round up to 2
    then need to check whether 2 present in Per Car Quantity

can anyone please suggest something if its possible in SQL

DECLARE @MYTABLE TABLE

(
Make VARCHAR (100),
Model VARCHAR (100),
Year VARCHAR (100),
Cylinders VARCHAR (100),
[Description] VARCHAR (100),
PER VARCHAR (100),
[Remarks=Is Per Car Quantity = Cylinders/Description] VARCHAR (500)

)

INSERT into @MYTABLE

SELECT 'Cadillac', 'DeVille', '1982', '15', 'OE Replacement; seven', '1', 'SHOULD BE IN OUTPUT BECAUSE 15/7 IS 2.14 but PER CAR QUANTITY IS 1 HERE' union all
SELECT 'SUZU', 'MSTAR', '2006', '6', 'OE Replacement; Single', '2', 'SHOULD BE IN OUTPUT BECAUSE 6/1 IS 6 but PER CAR QUANTITY IS 2' union all
SELECT 'FORD', 'FEISTA', '1991', '8', 'OE Replacement; Set Of 8', '1', 'ITS SHOULD EXCLUDE FROM OUTPUT because 8/8 IS 1 and PER CAR QUANTITY IS ALSO 1' union all
SELECT 'HONDA', 'CIVIC', '2005', '500', 'OE Replacement; FIFTY', '49', 'SHOULD BE IN OUTPUT BECAUSE 500/50 IS 10 but PER CAR QUANTITY IS 49' union all
SELECT 'BAJAJ', 'CHETAK', '2004', '15', 'OE Replacement; Set Of 3', '5', 'ITS SHOULD EXCLUDE FROM OUTPUT because 15/3 IS 5 and PER CAR QUANTITY IS ALSO 5'

select * from @MYTABLE

Thanks

2

If it were me :slight_smile: I would add a column for the parsed numeric value of the Description column, and then you can just do the maths on Cylinders, DescriptionNumber and PER

You could fiddle with the logic of the SQL code that populates the DescriptionNumber until you have parsed anything & everything in the Description which you possible can.

You could encapsulate the Parsing Logic in a VIEW instead, include the PKey columns for his table in the View, and then just JOIN the VIEW whenever you want to have the DescriptionNumber. But SQL is gogn to do the parsing every time if you do that, hence I think that parsing into a new column that maintains a fixed value would be better.

if the Description is changed then you either need to flag the "DescriptionNumber" as "Stale", or use a Trigger to apply the Parsing Logic as the record is inserted / updated so it is always correct.

3

Yikes, what a mess you have to deal with there! Yep, you need to use a trigger (or an extremely sophisticated computed column) to calculate and store the decoded value for the description column.

4

Thanks all.giving a try to it, but still not confident whether its possible. :smile:

5

I've done that sort of data parsing / interpretation before and I reckon we got about 80%, maybe 90%, successfully mechanically converted. We then did a report of the "Non converted" ones and the operators went through that and coded them up by hand.

6

I REALLY want to see that 500 cylinder Honda Civic. :open_mouth: