Extract Data with Select

Transact-SQL
,
1

I have a table (I only have read only access to the db) where one of the fields stores the classification

I want to be able to only have one of the various number of classifications be returned based on the following hierarchy:

If Client exists in the classification and Consultant and Architect doesn't appear then return 'End User'
If Architect exists in the classification then return 'Architect'
If Client or Architect don't exist return 'Professional'

Eg Classification field
Record 1
System Classifications\External Organisation - Architect - Consultant\Building Surveyor - Consultant\Project Management

Record 2
System Classifications\External Organisation - Client - Consultant\Building Surveyor - Consultant\Project Management

Record 3
System Classifications\External Organisation - Client

RESULT
Record 1 would return Architect
Record 2 would return Professional
Record 3 would return End User

Is this possible?

2

Should be, if I've understood your requirement correctly.

Something like this perhaps?

SELECT [MyClassifcation] =
            CASE WHEN classification LIKE '%Client%'
                      AND classification NOT LIKE '%Consultant%'
                      AND classification NOT LIKE '%Architect%'
                         THEN 'End User'
            WHEN classification LIKE '%Architect%'
                         THEN 'Architect'
            WHEN classification NOT LIKE '%Client%'
                      AND classification NOT LIKE '%Architect%'
                         THEN 'Professional'
            ELSE 'SomethingElse!!'
            END

However, this is fragile - it would also match "multiclient", and it is also highly reliant on the data having been entered "cleanly" - if any of the words were misspelled then you'll get an incorrect answer.

If it much better, in these circumstances, to have the relevant data in separate columns specifically so that you can reliably match it, and so that indexes can be used to speed up the query if performance becomes an issue. Or, if multiple values can be associated with a single record then those values should be stored in a separate table which is then associated with the Parent Table.

The other problem with this approach is that the "%" wildcard tests are slow. As the database grows so the query will take longer to process, so this type of solution does not scale well.

1 Like
3

Thanks seems to work well. Unfortunately I have no control over how the database works as its a 3rd party but the data is entered by choosing from a list so that negates the data entry problems. Thanks again for the quick response

4

Should be reasonably safe then :slight_smile: