@ Jeff Moden,

It concerns the hierchy as described in https://en.wikipedia.org/wiki/Anatomical_Therapeutic_Chemical_Classification_System

Now, this said, what I want to achieve is to have a Dimension which contains all hierarchies, and a fact...

SELECT *

FROM [Staging].[3NF_ATCCOLLECTION] ATC

WHERE ATC.[ATC_KEY] LIKE '[a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z][0-9][0-9]'

ORDER BY ATC.[ATC_KEY]

This results in records  that must be build as hierarchies...

I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently, some...

Found it :

SELECT

LEFT([ATC_KEY],1) AS [1st level]

,LEFT([ATC_KEY],3) AS [2nd level]

,LEFT([ATC_KEY],4) AS [3rd level]

....

Hey,

The implied level structure is always:

1st level: 1 letter, eg. C

2nd level: 2 digits , eg. C03

3rd level: 1 letter, eg. C03C

4th level: 1 letter, eg. C03CA

5th level: 2 digits,...