CODEWITHSUNDEEP
sqloracle-database
groogie13
groogie13

Reputation: 57

How to compare a part of a string between two columns

In the same table, I have two columns each having a string of the following format:

| column1                       | column2
--------------------------------|---------------------------------
| Soda: 10, Watter:5, Juice: 12 | Soda: 7, Watter:20, Juice: 15  |

How can I create a query where I extract only the rows where value for Water differs between column1 and column2?

Upvotes: 2

Views: 1510

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

Your columns are so close to being JSONs. If you are using Oracle 12c and above, consider storing them as JSONs. See JSON in Oracle. But, I would still say it is better to re-design your table by normalising it, unless it's absolutely necessary to store it this way.

If you are indeed using 12c+, converting them to JSON is also possible using JSON_VALUE to get individual elements for comparison.

SELECT *
FROM (
     SELECT '{' || column1 || '}' AS column1,
            '{' || column2 || '}' AS column2
     FROM t
)
WHERE 
JSON_VALUE(column1,'$.Watter' ) <> JSON_VALUE(column2,'$.Watter' );

I have not added extra validations. You must handle NULLs using COALESCE or NVL if the attribute Watter doesn't exist in some cases.

LiveSQL Demo ( Free OTN account required for execution)

Upvotes: 1

Jortx
Jortx

Reputation: 727

Try this way:

-- CREATE SAMPLE TABLE
;with strings as
(
SELECT 'Soda: 1, Watter:5, Juice: 4' AS Col1, 'Soda: 5, Watter:5, Juice: 12' AS Col2 UNION ALL
SELECT 'Soda: 2, Watter:1, Juice: 5', 'Soda: 7, Watter:2, Juice: 9' UNION ALL
SELECT 'Soda: 3, Watter:6, Juice: 6', 'Soda: 8, Watter:7, Juice: 10'
)

-- YOUR QUERY
SELECT  *
FROM    strings
WHERE   SUBSTRING(Col1,CHARINDEX('Watter:',Col1)+7,CAST(CHARINDEX(', Juice',Col1) AS int)-CAST(CHARINDEX('Watter:',Col1)+7 AS int))
<> SUBSTRING(Col2,CHARINDEX('Watter:',Col2)+7,CAST(CHARINDEX(', Juice',Col2) AS int)-CAST(CHARINDEX('Watter:',Col2)+7 AS int))

Upvotes: 0

George Joseph
George Joseph

Reputation: 5932

select substr(col1,instr(col1,'Watter:')+7,instr(col1,', Juice:')-instr(col1,', Watter:')-9) as from_col1
       ,substr(col2,instr(col2,'Watter:')+7,instr(col2,', Juice:')-instr(col2,', Watter:')-9) as from_col2
       ,col1
       ,col2
  from t
where substr(col1,instr(col1,'Watter:')+7,instr(col1,', Juice:')-instr(col1,', Watter:')-9)
    <> substr(col2,instr(col2,'Watter:')+7,instr(col2,', Juice:')-instr(col2,', Watter:')-9)

Upvotes: 0

Related Questions