SQL query to extract the part of matching SQL string-CodePudding

Let's consider a CLASS and BILL table.

CLASS table:

Class	Description
F	Factory
H	Headoffice
C001	Electrical
C002	Mechanical
C003	Civil

BILL table:

BILL_NO	DOCCLASS
1	FC001
2	FC002
3	FC003

Description: Class is a primary key. Let's say we created invoices by tagging different class from the CLASS table.

For example invoice 1 is created by tagging FCOO1 (ie. Factory and Electrical)

Problem: how to write a SQL query to get only COO1 part from FC001 of Invoice 1?

This SQL code is not working:

Select
    BILLDET.BILL_NO,
    BILLDET.CLASS,
    BILLDET.GLCODE,
    CLASS.DESCRIPT
From
    BILLDET 
Full Join 
    CLASS On BILLDET.CLASS = CLASS.CLASS

Output:

Bill NO	DESCRIPT
1	FactoryElectrical

Thank you

CodePudding user response：

Ugh, what a way to store data

WITH classx AS(
  SELECT
    c.class n.class as class,
    c.descript n.descript as descript 
  FROM
    class c 
    JOIN class n 
    ON 
      c.class LIKE 'c%' AND 
      n.class NOT LIKE 'c%'
)

SELECT * FROM bill JOIN classx ON bill.docclass = classx.class

CodePudding user response：

select bill_no, Description
from
(
select c2.class   c1.class as keys, c2.description c1.description as Description
from class c1, class c2
where c1.class LIKE 'c%' AND 
      c2.class NOT LIKE 'c%'
)as tab, bill
where keys = DOCCLASS;