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
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.
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
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