A simple string function to make a list in SQL

Test

We have a table with a list like:

RPT_name

All Divisions - All Units All Divisions - Corporate Business Development All Divisions - Flat Rolled All Divisions - Machinery Steels All Divisions - Non-Ferrous All Divisions - Plate Bar & Structural (Excl Plate Line) All Divisions - Plate Bar & Structural (Incl Plate Line) All Divisions - Plate Line All Divisions - Ultrawear B.C. Interior - All Units B.C. Interior - Corporate Business Development B.C. Interior - Flat Rolled                            B.C. Interior - Machinery Steels                                  B.C. Interior - Non-Ferrous                                        B.C. Interior - Plate Bar & Structural (Excl Plate Line)                  B.C. Interior - Plate Bar & Structural (Incl Plate Line) B.C. Interior - Plate Line B.C. Interior - Ultrawear                                          B.C. Lower Mainland and Island - All Units B.C. Lower Mainland and Island - Corporate Business Development B.C. Lower Mainland and Island - Flat Rolled                      B.C. Lower Mainland and Island - Machinery Steels                               B.C. Lower Mainland and Island - Non-Ferrous                                     B.C. Lower Mainland and Island - Plate Bar & Structural (Excl Plate Line)               B.C. Lower Mainland and Island - Plate Bar & Structural (Incl Plate Line) B.C. Lower Mainland and Island - Plate Line B.C. Lower Mainland and Island - Ultrawear                                       BC Region - All Units BC Region - Corporate Business Development BC Region - Flat Rolled                              BC Region - Machinery Steels                                  BC Region - Non-Ferrous                                        BC Region - Plate Bar & Structural (Excl Plate Line)                  BC Region - Plate Bar & Structural (Incl Plate Line) BC Region - Plate Line BC Region - Ultrawear                                          ...... Specialty Plate - All Units Specialty Plate - Corporate Business Development Specialty Plate - Flat Rolled Specialty Plate - Machinery Steels Specialty Plate - Non-Ferrous Specialty Plate - Plate Bar & Structural (Excl Plate Line) Specialty Plate - Plate Bar & Structural (Incl Plate Line) Specialty Plate - Plate Line Specialty Plate - Ultrawear Vancouver - All Units Vancouver - Corporate Business Development Vancouver - Flat Rolled                           Vancouver - Machinery Steels                                  Vancouver - Non-Ferrous                                        Vancouver - Plate Bar & Structural (Excl Plate Line)                  Vancouver - Plate Bar & Structural (Incl Plate Line) Vancouver - Plate Line Vancouver - Ultrawear                                          VCR Plasma - All Units VCR Plasma - Corporate Business Development VCR Plasma - Flat Rolled VCR Plasma - Machinery Steels VCR Plasma - Non-Ferrous VCR Plasma - Plate Bar & Structural (Excl Plate Line) VCR Plasma - Plate Bar & Structural (Incl Plate Line) VCR Plasma - Plate Line VCR Plasma - Ultrawear

We can break it down into two seperate fields using T-SQL language like:

1. SELECT [RPT_name]
   
2.       ,left([RPT_name],CHARINDEX(' - ', [RPT_name])-1) AS Divs
   
3.       ,substring([RPT_name],CHARINDEX(' - ', [RPT_name])+3, len([RPT_name])-CHARINDEX(' - ', [RPT_name])-2) AS Units
   
4.       ,CHARINDEX(' - ', [RPT_name]) AS Seps
   
5.   FROM [WDB].[dbo].[Report_List]
   
6. Order By Divs,Units

复制代码

We get the result as:

RPT_name	Divs	Units	Seps
Alberta - All Units	Alberta	All Units	8
Alberta - Corporate Business Development	Alberta	Corporate Business Development	8
Alberta - Flat Rolled	Alberta	Flat Rolled	8
Alberta - Machinery Steels	Alberta	Machinery Steels	8
Alberta - Non-Ferrous	Alberta	Non-Ferrous	8
Alberta - Plate Bar & Structural (Excl Plate Line)	Alberta	Plate Bar & Structural (Excl Plate Line)	8
Alberta - Plate Bar & Structural (Incl Plate Line)	Alberta	Plate Bar & Structural (Incl Plate Line)	8
Alberta - Plate Line	Alberta	Plate Line	8
Alberta - Ultrawear	Alberta	Ultrawear	8
All Divisions - All Units	All Divisions	All Units	14
All Divisions - Corporate Business Development	All Divisions	Corporate Business Development	14
All Divisions - Flat Rolled	All Divisions	Flat Rolled	14
All Divisions - Machinery Steels	All Divisions	Machinery Steels	14
All Divisions - Non-Ferrous	All Divisions	Non-Ferrous	14
All Divisions - Plate Bar & Structural (Excl Plate Line)	All Divisions	Plate Bar & Structural (Excl Plate Line)	14
All Divisions - Plate Bar & Structural (Incl Plate Line)	All Divisions	Plate Bar & Structural (Incl Plate Line)	14
All Divisions - Plate Line	All Divisions	Plate Line	14
All Divisions - Ultrawear	All Divisions	Ultrawear	14
...	...	...	...
VCR Plasma - All Units	VCR Plasma	All Units	11
VCR Plasma - Corporate Business Development	VCR Plasma	Corporate Business Development	11
VCR Plasma - Flat Rolled	VCR Plasma	Flat Rolled	11
VCR Plasma - Machinery Steels	VCR Plasma	Machinery Steels	11
VCR Plasma - Non-Ferrous	VCR Plasma	Non-Ferrous	11
VCR Plasma - Plate Bar & Structural (Excl Plate Line)	VCR Plasma	Plate Bar & Structural (Excl Plate Line)	11
VCR Plasma - Plate Bar & Structural (Incl Plate Line)	VCR Plasma	Plate Bar & Structural (Incl Plate Line)	11
VCR Plasma - Plate Line	VCR Plasma	Plate Line	11
VCR Plasma - Ultrawear	VCR Plasma	Ultrawear	11