Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- (CONVERT(VARCHAR(1000), CASE
- WHEN SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(09)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(08)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(07)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(06)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(05)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(04)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(03)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(02)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(01)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (4)) = '(00)' THEN SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (5), LEN(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END) - (4))
- ELSE CASE
- WHEN SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(Z)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(Y)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(X)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(W)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(V)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(U)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(T)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(S)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(R)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(Q)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(P)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(O)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(N)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(M)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(L)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(K)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(J)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(I)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(H)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(G)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(F)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(E)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(D)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(C)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(B)' OR
- SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (1), (3)) = '(A)' THEN SUBSTRING(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END, (4), LEN(CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END) - (3))
- ELSE CASE
- WHEN CHARINDEX(' ', [#ResultSet].[Sector]) > (2) THEN RTRIM(LTRIM(SUBSTRING([#ResultSet].[Sector], (0), CHARINDEX(' ', [#ResultSet].[Sector]))))
- ELSE LTRIM(RTRIM([#ResultSet].[Sector]))
- END
- END
- END, 0))
- FROM [#ResultSet];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement