Extract data

Transact-SQL
1

I have a column with a string that can be in any order. There can be any number of Use Classes but only 1 Type of Work. I have no control over how this record is produced
Eg
Use Classes\Common Parts - Use Classes\Internal Refurbishment - Type of Work\CAT A Fit-Out, Landlord Works - Use Classes\Some other use

I would like to return 2 columns
Type of Work
Use Classes

The record would look like
Column1: Type of Work: Type of Work\Cat A Fit-Out, Landlord Works
Column2: Use Classes: Use Classes\Common Parts - Use Classes\Internal Refurbishment - Use Classes\Some other use

Is this possible?

2

You could "split" the existing column on a "delimiter". That delimiter could be "-" or "Type of Work" etc.

Rather than getting out all the Use Classes bits and combining them into one column, and all the Type of Work bits and combing them into another column, it would probably be easier to split ALL the bits into a separate table one-element-per-row, and JOINING that "child table" to the master table.

You could re-combine them at that point, but data is generally much easier to handle if each element is in a separate row, rather than having a single column with multiple element values in it.

3

Since there can be only one Type_of_Work, I suggest finding it, extracting it and then removing it from the original string to leave the rest as the Use_Classes.

Here's sample code to do that:

SELECT
    string AS original_string,
    work AS type_of_work,
    CASE WHEN work_start = 0 THEN string ELSE
        STUFF(string, work_start - work_has_prefix * 3, work_end - work_start + (work_has_prefix * 3) - 
            CASE WHEN work_end > LEN(string) THEN 0 ELSE 3 END, '') END AS use_classes
FROM (
    VALUES('Use Classes\Common Parts - Use Classes\Internal Refurbishment - Type of Work\CAT A Fit-Out, Landlord Works - Use Classes\Some other use'),
          ('Use Classes\Common Parts - Use Classes\Internal Refurbishment - Use Classes\Some other use'),
          ('Use Classes\Common Parts - Use Classes\Internal Refurbishment - Type of Work\CAT A Fit-Out, Landlord Works')
) AS test_data(string)
CROSS APPLY (
    SELECT CHARINDEX('Type of Work', string) AS work_start,
        CHARINDEX('Use Classes', string + 'Use Classes', CHARINDEX('Type of Work', string) + 10) AS work_end
) AS find_work
CROSS APPLY (
    SELECT CASE WHEN work_start = 0 THEN '' ELSE SUBSTRING(string, work_start, work_end - work_start -
        CASE WHEN work_end > LEN(string) THEN 0 ELSE 3 END) END AS work
) AS extract_work
CROSS APPLY (
    SELECT CASE WHEN SUBSTRING(string, work_start - 3, 3) = ' - ' THEN 1 ELSE 0 END AS work_has_prefix
) AS check_work
1 Like
4

That's great. :smile: but I'm struggling to work out how this works :confused:

How do I fit that into a query?

I have this at the moment and somehow TypeOfWorkin my query needs to be the result of your type_of_work

SELECT DISTINCT
dbo.USU2VWMini_Search_Enquiry.Enquiry_Code, dbo.USU2VWMini_Search_Enquiry.Enquiry_ID, dbo.USU2VWMini_Search_Enquiry.Classification_Type AS TypeOfWork
FROM dbo.USEnquiry_Status INNER JOIN
dbo.USU2VWMini_Search_Enquiry ON dbo.USEnquiry_Status.Enquiry_Status_ID = dbo.USU2VWMini_Search_Enquiry.Enquiry_Status_ID INNER JOIN
dbo.USEnquiry_Type ON dbo.USU2VWMini_Search_Enquiry.Enquiry_Type_ID = dbo.USEnquiry_Type.Enquiry_Type_ID LEFT OUTER JOIN
dbo.USOrganisation ON dbo.USU2VWMini_Search_Enquiry.Client_Organisation_id = dbo.USOrganisation.Organisation_ID

5

I see that, but you have to understand that I had no idea what your full query was when you posted your q: I mean, how could I have, right? :slight_smile:

6

right - I know, I'm sorry. Is it possible to incorporate this into my query?

7

I've amended this to look at my data but if I'm going to link this to my original query I need to have the dbo.USU2VWMini_Search_Enquiry.EnquiryID included in the Output -is this possible, if yes, what do I need to do? I've tried various options but keep getting syntax errors

Output would just be:
EnquiryID, type_of_work, use_classes

SELECT
string AS original_string,
work AS type_of_work,
CASE WHEN work_start = 0 THEN string ELSE
STUFF(string, work_start - work_has_prefix * 3, work_end - work_start + (work_has_prefix * 3) -
CASE WHEN work_end > LEN(string) THEN 0 ELSE 3 END, '') END AS use_classes
FROM (
SELECT Classification_Type FROM dbo.USU2VWMini_Search_Enquiry
) AS test_data(string)
CROSS APPLY (
SELECT CHARINDEX('Type of Work', string) AS work_start,
CHARINDEX('Use Classes', string + 'Use Classes', CHARINDEX('Type of Work', string) + 10) AS work_end
) AS find_work
CROSS APPLY (
SELECT CASE WHEN work_start = 0 THEN '' ELSE SUBSTRING(string, work_start, work_end - work_start -
CASE WHEN work_end > LEN(string) THEN 0 ELSE 3 END) END AS work
) AS extract_work
CROSS APPLY (
SELECT CASE WHEN SUBSTRING(string, work_start - 3, 3) = ' - ' THEN 1 ELSE 0 END AS work_has_prefix
) AS check_work

8

Worked it out. Thanks for your help