Hey! Matt Miller! This looks like a great place for one of those awesome Regex things you do! 🙂

RayM,

In the absense of Regex, you first need to build a Tally table to make your life easier for a lot of things. A Tally table is nothing more than a table with a single column of very well indexed sequential numbers... it's used to replace loops and the like... here's how to build one...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

Once that's done, your problem becomes a whole lot easier and doesn't require performance robbers like the REVERSE function...

SELECT s.RowID,

Type = MAX(CASE WHEN s.Posit =1 AND LEN(s.SplitString) =2 THEN s.SplitString ELSE NULL END),

Code = MAX(CASE WHEN s.Posit =2 AND LEN(s.SplitString)<=5 THEN s.SplitString ELSE NULL END)

FROM (--==== Derived table "s" splits all of the strings and marks the position (ordinal index) of each

SELECT RowID,

SplitString = SUBSTRING(' '+h.String+' ', t.N+1, CHARINDEX(' ', ' '+h.String+' ', t.N+1)-t.N-1),

Posit = t.N-LEN(REPLACE(LEFT(' '+h.String+' ',t.N), ' ', ''))

FROM dbo.Tally t

RIGHT OUTER JOIN --Necessary in case String is NULL

#Test h

ON SUBSTRING(' '+h.String+' ', t.N, 1) = ' '

AND t.N < LEN(' '+h.String+' ')

AND h.String NOT LIKE '%[_]%'

)s

WHERE s.Posit IN (1,2)

GROUP BY s.RowID